databasedev.co.uk - database solutions and downloads for microsoft access

Query Progress Meter

Creating a Progress meter for use whilst running Microsoft Access Queries:

Looking for that extra special effect to give your database project a professional flair.

Sometimes, whilst generating query results, your user's may be left waiting especially if you are querying a large record source.

If the answer is yes then carry on reading.........

Step 1. If you are generating your query from a command button on a form:Add the following code the the buttons On Click event.

'----------------- Code Start ------------------

Private Sub [Your_Command_Button_Name]_Click()
    Dim i As Double
    Dim ReturnValue As Variant
    Dim maxvalue As Double
    Dim stepp As Double
    Dim X As Variant
    Dim Total As Double

    maxvalue = 700000
    stepp = 100 / maxvalue

    ReturnValue = SysCmd(SYSCMD_INITMETER, "Building query", 100)

    Response = MsgBox("This may take some time." _
                    & Chr(10) & Chr(13) & _
                    "Push OK to continue, and Cancel to quit", 1)
    If Response = 1 Then
        DoCmd.Hourglass True
        For i = 1 To maxvalue
            Total = Total + stepp
            X = Int(Total)
            ReturnValue = SysCmd(SYSCMD_UPDATEMETER, X)
        Next i
        DoCmd.Hourglass False
        DoCmd.OpenQuery ("YOUR_QUERY_NAME")
    End If

    ReturnValue = SysCmd(SYSCMD_SETSTATUS, "Updating query")
    ReturnValue = SysCmd(SYSCMD_REMOVEMETER)

End Sub

'------------------ Code End -------------------

Step 2. Ensure that you amend: the values for the "Command_Button_Name" to the name of your command button that the query will be generated from and for "Your_Query_Name" to the name of the query.

Step 3. Test the command button: If you have followed the above steps you should now be able to click on your command button, and you should see the following occur.

Click On the command button to run the search query

If you OK the message you will see the progress meter running in your status bar.

Building Progress Meter

This article should now assist you in building a working progress meter to include within any of your databases.