When you create a Recordset, you may want to know immediately whether that Recordset actually contains any Rows. There are Recordsets that don't return any Rows and you may need to take different steps based on this outcome. There are basically 3 Methods for testing for an Empty Recordset (Recordset that returns no Rows). We will be using DAO, but these Methods are equally applicable to ADO.
- 'Common Code Block
-
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
-
Set MyDB = CurrentDB()
-
Set MyRS = MyDB.OpenRecordset("qryEmployees", dbOpenDynaset)
-
- 'Method #1
-
If Not MyRS.BOF And Not MyRS.EOF Then
-
'you will only be here if Rows are returned
-
End If
-
- 'Method #2
-
Do Until MyRS.EOF
-
'if the Recordset returns no Rows, Loop will not be entered
-
Loop
-
- 'Method #3
-
If MyRS.Recordcount > 0 Then
-
'you will only be here if Rows are returned
-
End If
-
- NOTE: If you are looking for an exact Record Count in addition
-
to testing for an Empty Recordset, it is a good idea to traverse
-
the Recordset beforehand as in:
-
-
MyRS.MoveLast: MyRS.MoveFirst
-
Debug.Print MyRS.RecordCount
-
-
------------------------------------------------------------------------------
-
- NOTE: Any explicit move in an Empty Recordset such as MoveNext,
-
MoveLast will result in a Runtime Error.
