How can I speed up running several queries to populate data on a form?

I have a form that I has (4) command buttons. The caption on the command buttons will display the record count of a query.  The forms On Current Events runs (4) queries for record counts on each. It takes about 10 seconds to load the form. Is the code correct? Is there a way to speed it up?
Private Sub Form_Current()
Dim rsUpdatesCount As Integer
Dim rsActiveCasesCount As Integer
Dim rsActivitiesCount As Integer
Dim rsTasksCount As Integer
Dim queryNameOrSQL As String
Set db = CurrentDb
queryNameOrSQL = "SELECT * FROM QueryAllUpdates"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
rsUpdatesCount = rs.RecordCount
Me.CmdUpdates.Caption = "Updates " & rs.RecordCount
' Next Query
queryNameOrSQL = "SELECT * FROM QuerySubActive"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
rsActiveCasesCount = rs.RecordCount
Me.CmdActiveCases.Caption = "Active Cases " & rs.RecordCount
'Next Query
queryNameOrSQL = "SELECT * FROM QuerySubTasks"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
rsTasksCount = rs.RecordCount
Me.CmdTask.Caption = "Tasks " & rs.RecordCount
'Next Query
queryNameOrSQL = "SELECT * FROM QuerySubActivity"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
If Not (rs.BOF And rs.EOF) Then
rsActivitiesCount = rs.RecordCount
Me.CmdActivity.Caption = "Activities " & rs.RecordCount
End If
Set rs = Nothing

End Sub

Open in new window

Who is Participating?
Selecting * certainly doesn't help when all you want is a record count.
DCount maybe your friend here
Try this where you change SomePrimaryKey to be some field that exists in every record of that query

Private Sub Form_Current()
Dim queryNameOrSQL As String

queryNameOrSQL = "QueryAllUpdates"
Me.CmdUpdates.Caption = "Updates " & nz(Dcount("SomePrimaryKey",queryNameOrSQL),0
' Next Query
queryNameOrSQL = "QuerySubActive"
Me.CmdActiveCases.Caption = "Active Cases " & nz(Dcount("SomePrimaryKey",queryNameOrSQL),0
'Next Query
queryNameOrSQL = "QuerySubTasks"
Me.CmdTask.Caption = "Tasks " &  nz(Dcount("SomePrimaryKey",queryNameOrSQL),0
'Next Query
queryNameOrSQL = "QuerySubActivity"
Me.CmdActivity.Caption = "Activities " & nz(Dcount("SomePrimaryKey",queryNameOrSQL),0)

End Sub

and you can clean up your code by refactoring that stuff into a function

private function ButtonCount(byval pstrTitle as String, byval pstrKey as String, byval pstrSQL as string) as String
    ButtonCount = pstrTitle & " " &  format(nz(Dcount(pstrKey, pstrSQL),0))
end function

then your code looks like

me.cmdUpdates.Caption = ButtonCount("Updates", "YourKeyField", "QueryAllUpdates")
me.cmdActiveCase.Caption = ButtonCount("Active Cases", "KeyField", "QuerySubActive")

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.