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:
Figure 5.2 Relationship between the current record position and the BOF and EOF properties.