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).
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.
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.
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.
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields