Access Tip – Generic function to return the number of records

The following are some samples of VBA code that may prove useful.

This is a generic function to check the number of records. In the calling program, create an SQL statement stored in a string. Say you want to know the number of records and store in lngRecs. If the string was called strSQL, you would call this function using

Dim lngRecs as Long
Dim strSQL as String

strSQL = “SELECT * from tblNames”

lngRecs = funRecordCount(strSQL)

Public Function funRecordCount(strSQL As String) As Integer
Dim dbs As Database
Dim rst As Recordset

On Error GoTo Error_funRecordCount

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

‘ Find the number of records. First test records were found.
If (rst.EOF = True) And (rst.BOF = True) Then

funRecordCount = 0 ‘ No records found

Else

rst.MoveLast ‘ End of the recordset
funRecordCount = rst.RecordCount ‘ Number of records

End If

Exit_funRecordCount:
On Error GoTo 0
Set dbs = Nothing
Set rst = Nothing
Exit Function

Error_funRecordCount:
MsgBox “An unexpected situation arose in your program.” & funCrLf & _
“Please write down the following details:” & funCrLf & funCrLf & _
“Module Name: modGeneric” & funCrLf & _
“Type: Module” & funCrLf & _
“Calling Procedure: funRecordCount” & funCrLf & _
“Error Number: ” & Err.Number & funCrLf & _
“Error Descritption: ” & Err.Description

Resume Exit_funRecordCount

End Function