Progress Meter ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When we process a large volume of data for MS-Access Reports it may take a few minutes to several minutes to complete, depending on the transaction volume involved. The transactions are organized and processed through Select Queries, and Action Queries, sequencing the process through Macros and VBA routines to arrive at the final Report Data. If you have Action Queries that pull data from Select, Crosstab Queries, and if a large volume of transactions is involved, it may take longer than the Normal Queries to filter for output.

In all these situations it will be difficult for the user to know how long the whole process will take to complete the task. After running the process a few times the user may get a rough idea as to how long it will take to finish.

Normally at the beginning of a lengthy process, the Mouse Pointer can be turned on into an Hourglass shape (Docmd.Hourglass True) indicating that the machine is engaged, and can be turned it off at the end of the process. But, this method will not give an exact indication as to when the process will be over.

If it takes more than the usual running time, depending on other factors, like an increase in the volume of transactions or due to busy network traffic and so on, it is difficult to determine whether the process is really running or it is a machine hang up issue.

The Quick Solution

When we run several Action Queries in a chain from within a Macro, MS-Access displays a Progress Meter for a brief moment for each Query on the Status Bar. If the Status Bar is not visible you can turn it on. Select Options from the Tools menu, and select View Tab on the displayed Dialog Control. Put a check-mark in the Status Bar option, under Show Options Group. But it will not give an overall time indicator for the full process.

A Better Approach.

We will make use of the Progress Meter for our data processing tasks more effectively and will look into more than one method. The users of our Application can relax during the whole process and take little time off to flip through the Weekly Magazine with an occasional glance at the Progress Meter.

Displaying the Progress Meter on the Status Bar
Displaying the Progress Meter on a Form
Usage of a transaction countdown method.

Usage of Progress Meter on the Status Bar.

We will use the Order Details Table from the Northwind.mdb sample database for our example and write a VB Routine to calculate the Extended Price on each entry in this Table. If you have not already imported this Table for our earlier examples you may do it now. If you don't know the exact location of this file on your machine, please visit the Page Saving Data on Forms not in Table for references.

Import the Order Details Table from Northwind.mdb sample Database.
Open the Order Details Table in Design View and add a new field with the name ExtendedPrice (Field Type: Number, Field Size: Double) at the end of the existing fields. We will write a program to calculate the extended price of each record and update this field.
Design a simple form similar to the one shown below with a Command Button on it. We will modify this Form for our next example also.
Click on the Command Button and display the Property Sheet (View -> Properties).
On the On Click Property type =ProcessOrders() to run the program, which we are going to write now. Do not forget the equal sign in =ProcessOrders(), otherwise, MS-Access will take it as a Macro name.
Close the form and save it with the name ProgressMeter.

Usage of SysCmd().

Copy and Paste the following Code into a Global VB Module of your Project and save it.

Public Function ProcessOrders()
Dim db As Database, rst As Recordset
Dim TotalRecords As Long, xtimer As Date
Dim ExtendedValue As Double, Quantity As Integer
Dim Discount As Double, x As Variant, UnitRate As Double

On Error GoTo ProcessOrders_Err

DoCmd.Hourglass True

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLast
TotalRecords = rst.RecordCount

rst.MoveFirst
Do While Not rst.EOF
  With rst
    Quantity = ![Quantity]
    UnitRate = ![UnitPrice]
    Discount = ![Discount]
    ExtendedValue = Quantity * (UnitRate * (1 - Discount))

    .Edit
    ![ExtendedPrice] = ExtendedValue
    .Update

    If .AbsolutePosition + 1 = 1 Then
       x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords)
    Else
      'a delay loop to slow down the program       
      'to view the Progress Meter in action.      
      'you may remove it.      
'=================================================
      xtimer = Timer
      Do While Timer < xtimer + 0.02
        Doevents
      Loop
'=================================================

      x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1)
    End If

   .MoveNext
  End with
Loop
rst.Close
x = SysCmd(acSysCmdRemoveMeter)
DoCmd.Hourglass False

MsgBox "Process completed.", , "ProcessOrders()"

Set rst = Nothing
Set db = Nothing

ProcessOrders_Exit:
Exit Function

ProcessOrders_Err:
MsgBox Err.Description, , "ProcessOrders()"
Resume ProcessOrders_Exit

End Function

Open the ProgressMeter Form in normal view and click on the Command Button. You will see the Progress Meter slowly advancing and when it reaches the end of the bar a message will appear announcing that the work is complete.

We have used MS-Access's built-in Function SysCmd() to update the Progress Meter on the Status Bar. When the Function is first called, the Maximum Number of Records in the File is passed as the third parameter to the function to initialize the Progress Meter. Subsequent calls are made with the current record number to update the Meter with the current status. MS-Access calculates a percentage of the current number of records processed based on the Total Records that we have passed to the InitMeter step and updates the Progress Meter. The blue-colored indicator on the Progress Meter may advance one step, only after processing several records depending on the total number of records in the file.

A delay loop is built into the Code to slow down the program and view the Progress Meter in action. You may remove these lines when using them in your Project.

Need a Better Method

We cannot use this method when we sequence our process steps in Macros involving Queries. Because, when each Action Query is run MS-Access uses the Status Bar to display the progress of each and every query separately overwriting our overall process time meter. We have to devise a method of our own to do this.

Next, we will see the usage of a Progress Meter on a Form, for the Data Processing steps sequenced through Macro.