Testing for an Empty Recordset

[ADezii]

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.