[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-06-21
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

Question by:seanlhall
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 36921570
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

LVL 16

Expert Comment

ID: 36921707
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")


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question