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)
rs.MoveLast
rsUpdatesCount = rs.RecordCount
Me.CmdUpdates.Caption = "Updates " & rs.RecordCount
' Next Query
queryNameOrSQL = "SELECT * FROM QuerySubActive"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rsActiveCasesCount = rs.RecordCount
Me.CmdActiveCases.Caption = "Active Cases " & rs.RecordCount
'Next Query
queryNameOrSQL = "SELECT * FROM QuerySubTasks"
Set rs = CurrentDb.OpenRecordset(queryNameOrSQL, dbOpenDynaset, dbSeeChanges)
rs.MoveLast
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
rs.MoveLast
rsActivitiesCount = rs.RecordCount
Me.CmdActivity.Caption = "Activities " & rs.RecordCount
Else
End If
rs.Close
Set rs = Nothing
End Sub
Who is Participating?
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")
etc.
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")
etc.
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.
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