29.4. Accessing a Particular Record in a Recordset

To access a particular record in a recordset, you can either move through the records to find the one you want, or search for the record using Seek or Find methods. The RecordSet object includes these methods for moving about the records in the recordset:

29.4.1. Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods

The MoveFirst method and MoveLast method are always safe to use, because as long as the recordset contains one or more records, there's always a first record and a last record. (If the recordset contains only one record, that record is both the first record and the last record.) But if you use the MovePrevious method from the first record in the recordset or the MoveNext method from the last record, you move beyond the recordset, accessing what is sometimes called a "phantom record"—one that isn't there. When you try to access the contents of the record, VBA gives the runtime error 3021 ("Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."). Figure 29.1 shows this error.

Figure 29.1. The runtime error "Either BOF or EOF is True..." usually means that you've moved outside the recordset by using the MoveLast method from the last record or the MovePrevious record from the first record in the recordset.

BOF is the beginning of the file, and EOF is the end of the file. To check whether you're at the beginning or end of the recordset, use the BOF property or the EOF property of the RecordSet object. The BOF property returns True when the current record is at the beginning of the file, and the EOF property returns True when the current record is at the end of the file. To avoid errors, check after using the MovePrevious method whether the beginning of the file has been reached—for example:

With myRecordset
    .MovePrevious
    If .BOF = True Then .MoveNext
End With

Similarly, check after using the MoveNext method whether the end of the file has been reached—for example:

myRecordset.MoveNext
If myRecordset.EOF Then myRecordset.MovePrevious

29.4.2. Using the Move Method to Move by Multiple Records

To move by several records at once, but not to the first record or last record in the recordset, use the Move method. The syntax is different for ADO and for DAO.

Here's the syntax for the Move method with ADO:

recordset.Move NumRecords, Start

The syntax for the Move method with DAO is as follows:

recordset.Move Rows, StartBookmark

Here, recordset is the recordset involved, NumRecords or Rows is the number of records by which to move (use a positive number to move forward or a negative number to move back), and Start or StartBookmark is an optional argument that you can use to specify the bookmark from which you want to start the movement. If you omit Start or StartBookmark, movement starts from the current record.

For example, the following statement moves ten records forward from the current record in an ADO recordset:

myRecordset.Move NumRecords:=10

The following statement moves five records backward from the current record in a DAO recordset:

myRecordset.Move Rows:=-5

To create a bookmark, move to the record that you want to mark, and then use the Bookmark property of the RecordSet object. The following example declares a Variant variable named myBookmark and then assigns to it a bookmark representing the current record in an ADO recordset:

Dim myBookmark As Variant
myBookmark = myRecordset.Bookmark

After setting a bookmark, you can use it as the starting point of a move. For example, the following statement moves to the eighth record after the bookmark myBookmark in an ADO recordset:

myRecordset.Move NumRecords:=8, Start:=myBookmark