Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure
Microsoft Access Development Tips

Home - Microsoft Access Development - Microsoft Access Tips

Sample Microsoft Access VBA

Generic function to return 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

 

Return to the top