Detecting the Limits of a Recordset

In a Recordset object, if you try to move too far in a direction, a run-time error occurs. For example, if you try to use the MoveNext method when you’re already at the end of the Recordset object, a trappable error occurs. For this reason, it’s helpful to know the limits of the Recordset object.

The BOF property indicates whether the current position is at the beginning of the Recordset object. If BOF is True, the current position is before the first record in the Recordset object. The BOF property is also True if there are no records in the Recordset object when it’s opened. Similarly, the EOF property is True if the current position is after the last record in the Recordset object or if there are no records in the Recordset object.

The following example shows how to use the BOF and EOF properties to detect the beginning and end of a Recordset object. This code fragment creates a table-type Recordset object based on the Employees table. It moves through the records, first from the beginning of the Recordset object to the end, and then from the end of the Recordset object to the beginning. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)

With rst
	' Move to first record in recordset.
	.MoveFirst
	Do Until .EOF
		' Print value of data.
		Debug.Print rst!LastName
		' Move to next record.
		.MoveNext
	Loop
	Debug.Print
	' Move to last record.
	.MoveLast
	Do Until .BOF
		Debug.Print rst!LastName
		' Move to previous record.
		.MovePrevious
	Loop
	.Close
End With

Note that there’s no current record immediately following the first loop. The BOF and EOF properties both have the following characteristics:

• If the Recordset object contains no records when you open it, both BOF and EOF are True.
  
  
• When BOF or EOF are True, they remain True until you move to an existing record, at which time the value of BOF or EOF becomes False.
  
  
• When BOF or EOF is False, and the only record in a Recordset object is deleted, the property remains False until you attempt to move to another record, at which time both BOF and EOF become True.
  
  
• At the moment you create or open a Recordset object that contains at least one record, the first record is the current record, and both BOF and EOF are False.
  
  
• If the first record is the current record and you use the MovePrevious method to move past the first record in the Recordset object, BOF is set to True. If you use the MovePrevious method while BOF is True, a run-time error occurs. When this happens, BOF remains True and there is no current record.
  
  
• Similarly, moving past the last record in the Recordset object changes the value of the EOF property to True. If you use the MoveNext method while EOF is True, a run-time error occurs. When this happens, EOF remains True and there is no current record.

  Figure 5.2 Relationship between the current record position and the BOF and EOF properties.