If you move beyond the boundaries of a recordset, an error will occur. To avoid this rather unpleasant side effect of poor programming practice, you should test to see whether you have reached the beginning or end of the recordset. Make sense?
Before using MoveNext or MoveFirst, you should check the value of BOF and EOF.
If Not rst.BOF Then rst.MovePrevious
If Not rst.EOF Then rst.MoveNext
To help you understand the behavior of these properties, consider the following scenarios:
- You issue MoveNext while the cursor is on the last row, and EOF returns True. You then issue MoveNext again, EOF remains True, and an error occurs.
- You issue MovePrevious while the cursor is on the first row, and BOF returns True. You then issue MovePrevious again, BOF remains True, and an error occurs.
- AbsolutePosition can be used to test for an empty recordset, but it cannot be used on tabletype recordsets, so you need another method (discussed in the next section) for determining whether a recordset contains any records.
- BOF and EOF are widely used when looping through recordsets, when you don't know how many records have been returned. Usually, row processing begins at the first row, and continues unil all the rows have been processed. Sometimes, however, processing begins at the last record, and continues backward until the beginning of the recordset. BOF and EOF allow you to do this.
For example, the following code shows a standard forward looping construct:
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) Do While Not rst.EOF 'Process the rows rst.MoveNext Loop
The following example demonstrates a typical reverse-direction loop:
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) rst.MoveLast Do While Not rst.BOF 'Process the rows rst.MovePrevious Loop
Testing for an Empty Recordset
As mentioned in the previous section, if you attempt to move beyond a recordset's boundaries, an error occurs. Similarly, if you attempt to execute any other recordset method on an empty recordset (one that has not returned any records), an error occurs.
Whenever you open a recordset, you usually want to do something with the data it returns, so the first thing you need to know is whether it returned any records. If the data is there, you can confidently take whatever actions you had planned. But if, for whatever reason, the recordset doesn't return any records, you have to take some alternative action such as displaying a message to the user or simply exiting the routine.
Testing for an empty recordset can be accomplished in several ways:
- Test for AbsolutePosition, as described earlier.
- Test for BOF and EOF together. If BOF and EOF are both True, the recordset is empty. For example:
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) If Not (rst.BOF And rst.EOF) Then 'The recordset returned records End If
- If you need to loop through the recordset, create a condition test that can't be met in the event of an empty recordset. For example:
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) Do Until rst.EOF 'The recordset returned records Loop
- Check the recordset's RecordCount property. If it is zero, you know there aren't any records. For example:
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset) If rst.RecordCount > 0 Then 'The recordset returned records End If
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