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

Posted on 2011-10-05
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

    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

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now