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: