MS-Access / Getting Started

---

BOF, EOF

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

or

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