Testing for an Empty Recordset

As mentioned in the previous section, if you attempt to move beyond a recordset's boundaries, an error will occur. 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, the first thing you need to know is, "did it return any records?" When you open a recordset, you usually want to do something with the data it returns. 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 will have to take some alternative action.

Testing for an empty recordset can be accomplished in several ways:

1. Test for AbsolutePosition, as described earlier.

2. 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

3. 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

4. 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

0 0

Responses

  • luisa monaldo
    How to test for an empty recordset access 2007?
    7 years ago

Post a comment