In a recent program that I wrote, I needed to store a count of records from a query in a variable. Since VBA and Access isn’t as intuitive as say connecting to a MySQL database with PHP, I thought I would share.
Function recordCount()
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
'Count Query - replace query with your
'Query of Choice
strSQL = "SELECT COUNT(*) FROM [table]"
'Set Recordset Query
Set rs = db.OpenRecordset(strSQL)
'Return Record Count Variable
recordCount = rs.Fields(0)
'Close Connections and Reset Variables
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
I use this code in a module. To insert a module, select menu Insert>Module. Paste this code and modify.
When calling in a program you use recordCount()
Dynamically Count Records in Table
With this method, you can count records by using recordCount(“tablename”). For some reason I left this out when writing this post.
Function recordCount(selTable)
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
'Count Query - replace query with your
'Query of Choice
strSQL = "SELECT COUNT(*) FROM " & selTable
'Set Recordset Query
Set rs = db.OpenRecordset(strSQL)
'Return Record Count Variable
recordCount = rs.Fields(0)
'Close Connections and Reset Variables
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Anyway, that is all I got for this post. Please feel free to comment with suggestions or improvements.
Good, For the beginer VBA Code , Thank you.
Hello from Hamburg, Germany.
Thank you for this tiny code.
In an business DB with tasks and actions (many per task) I needed to build an myID for (new) actions like autoincrement but always starting from 1 per task. With your code I can count the actions records with same fk to tasks and use the result+1 as default value for myID when creating new actions via form.
Dear Sir
Can you make a exemple for function “recordCount”?