Checking if a Recordset has any records

Checking if a Recordset has any records

Often, when using a Recordset, you will want to perform various actions with the records that are in the Recordset. If there are no records returned, then you will get an error, usually 3021 ('No current record') as you try to perform any actions on the records, as the example below shows:

Public Sub sNoRecords()
'   Procedure to illustrate what happens when no records are in a Recordset
    On Error GoTo E_Handle
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("SELECT * FROM tblFoo WHERE 1=2;")
    Do
        Debug.Print rs(0)
        rs.MoveNext
    Loop Until rs.EOF
sExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & "sNoRecords", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

There are several ways of checking if the Recordset has records. The firsts method checks if the Recordset's .RecordCount property is not equal to 0. This can be used on native Access tables, and also tables that are linked from other Access databases:

Public Sub sCheckRecords1()
'   Procedure to illustrate one way of checking if a Recordset has records in it.
    On Error GoTo E_Handle
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("SELECT * FROM tblFoo WHERE 1=2;")
    If rs.RecordCount <> 0 Then
        Do
            Debug.Print rs(0)
            rs.MoveNext
        Loop Until rs.EOF
    End If
sExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & "sCheckRecords1", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

The second method checks whether the Recordset's .BOF (Beginning Of File) and .EOF(End Of File) properties are True, as the only time that both of these can be True is if there are no records:

Public Sub sCheckRecords2()
'   Procedure to illustrate another way of checking if a Recordset has records in it.
    On Error GoTo E_Handle
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("SELECT * FROM tblFoo WHERE 1=2;")
    If Not (rs.BOF And rs.EOF) Then
        Do
            Debug.Print rs(0)
            rs.MoveNext
        Loop Until rs.EOF
    End If
sExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & "sCheckRecords2", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Top

Last modified at 06/06/2006 14:57:19