MS-Access / Getting Started

---

Navigating Recordsets

Once you've opened a recordset, you'll probably want to get at its data and you'll probably want to move from record to record.

DAO provides five methods and five properties to help you navigate through your recordsets. The methods are Move, MoveFirst, MovePrevious, MoveNext, and MoveLast. The properties are AbsolutePosition, PercentPosition, RecordCount, BOF (beginning of file), and EOF (end of file).

Navigational Methods

The Recordset object's Move method enables you to move the cursor to another position relative to either the current position, or that specified by a Bookmark. The Move method provides two arguments.

rst.Move rows[, start]

The rows argument specifies the number of rows to move, and the direction: greater than zero indicates forward, less than zero means backward. The optional start argument specifies where to start the move. When you supply a Bookmark (discussed later in this tutorial) for the start argument, DAO moves the cursor the appropriate number of rows from the position specified by the Bookmark. If you omit the start argument, DAO moves the cursor from the current position.

MoveFirst, MovePrevious, MoveNext, and MoveLast are the workhorses of recordset navigation, particularly MoveNext and MovePrevious. As their names suggest, they allow you to move the cursor forward and backward from the current position.

AbsolutePosition, PercentPosition

The AbsolutePosition and PercentPosition methods enable you to move the cursor to a specific row in the recordset. For example, if you wanted to move to the 127th row, you could issue the following method call:

rst.AbsolutePosition = 127

Similarly, to move to (roughly) half-way through the recordset, you could issue this:

rst.PercentPosition = 50

AbsolutePosition does not equate to a row number, and although it does return the cursor's current position in the recordset, that position can change as you add or delete rows, or change your filtering and sorting. You can't use AbsolutePosition with table-type recordsets.

RecordCount

Given its name, you might assume that the RecordCount property actually indicates the number of records returned by a recordset. That assumption is not quite accurate.

Recordsets do not always return their entire dataset immediately; they can take quite some time to populate; the more rows they have to return, the longer they take. DAO returns a pointer to the recordset early, so you can get on with doing whatever it is you want to do, assuming that the later rows will have been returned by the time you get to them.

The RecordCount property actually returns the number of rows that the recordset has accessed so far. Of course, if you issue the MoveLast method before checking RecordCount, the recordset does not return until all the records have been accessed, in which case RecordCount then reports the correct number of rows. In fact, that's how you get an accurate record count, by issuing a MoveLast, followed by checking the RecordCount property, as the following example shows. Note that this technique does not work with forward-only recordsets.

Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)

If rst.AbsolutePosition > -1 Then
    'Move to the last row
    rst.MoveLast

    'Now get the count
    lngCount = rst.RecordCount

    'If you want, you can now move again
    rst.MoveFirst

    '----
    'Continue processing
    '----

End If

RecordCount always returns the correct number of rows for table-type recordsets.

In a single-user environment, once RecordCount has the correct number of rows, it stays synchronized when rows are added or deleted. In a multiuser environment, however, things get a little trickier.

For example, if two users are modifying records in the same table, additions or deletions made by one user will not be reflected on the other user's computer until they access that record (or the place where a deleted record used to be). To ensure you have an accurate record count in a multiuser environment:

• Use the recordset's Requery method (see the following note); or
• Use the MoveLast method again.

The Requery method is not supported on table-type recordsets. The RecordCount property for snapshot- type recordsets will not change once it has been created, and it certainly won't reflect changes made by other users.