|
Quick Tip Details |
Question:
Using VBA, how can I count the number of records in a table or query. |
Answer:
This function can be saved into a module. Simply pass the SQL Select statement to the function to receive the total number or records. |
Code:Function SQLCount_MEI(SQL As String) As Long
'------------------------------------------------------------------------------------------------------------
'Comments: Counts the total number of records in a SQL Statement
'Parameters: SQL-SQL Statement
'Returns: Total Records if sucessful; -1 if the function failed
'------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset(SQL)
With rst
If .EOF Then
SQLCount_MEI = 0
Else
.MoveLast
SQLCount_MEI = .RecordCount
End If
.Close
End With
Set db = Nothing
Err_Trap_Exit:
Exit Function
Err_Trap:
SQLCount_MEI = -1
MsgBox Err.Description
Resume Err_Trap_Exit
End Function |
|
