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

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 26

Nick67Commented: 2011-10-05

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 16

kmslogicCommented: 2011-10-05

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.

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

Who is Participating?