Progress Bar on Form

Introduction.

Continued from the previous article: Progress Meter.

We will try the Progress Bar Control on the sample Form that we have already designed earlier, for use with Macros.

Note: This method of using a Progress Bar within a Macro was found to work reliably only in Microsoft Access 2000. When tested in Access 2003, the Progress Bar control failed to update, regardless of the database format used (Access 2000, 2002, or 2003). If any readers have discovered a workaround or solution for this issue, I would greatly appreciate it if you could share it with me.

That said, you can still apply this technique with our earlier VBA program by replacing the SysCmd() lines. This allows you to display the Progress Bar directly on your own Form, instead of relying on the Status Bar, for more controlled and user-friendly transaction-level processing feedback.

The Form Design

1. Open the 'ProgressMeter' Form in Design View.

2. Select the ActiveX Control Option from the Insert Menu.

3. Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.

4. Draw a Progress Bar on the Form as shown in the image below. Resize the Form to make it look like the sample below. You can make it in any size and shape you prefer.

   

5. Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View --> Properties).

6. Need to change the following Property Values as shown below:

   • Name = PB2
   • Visible = False

7. Create a label on the left side of the ProgressBar. Display its Property Sheet and change the following values:

   • Name = lblStatus
   • Visible = False

8. Select the Command Button on the Form, display the Property Sheet, and change the On Click Property.

   • On Click = Spl_Report

   Spl_Report is a Macro in which we have sequenced our processing steps, and the Progress Bar will show the current status of its activity.

9. Save the Form after the changes.

10. Select Options from the Tools Menu. Uncheck the Status Bar under the Show 'Option-group' on the View Tab. Click Apply, and click OK to close the control.

11. If you have a Macro running several steps of Queries, Macros, and Code, make a copy and rename it as Spl_Report, and modify it for our demo. A sample Macro Image is given below for reference.

    
    
    

    The Macro Action Lines

    The first two steps on the Macro's Action Field have their values set as No.

    The third line calls the ProgMeter2(1, 5, "PB2") Function with the RunCode macro command with three parameters to initialize the Progress Meter. The Values of the parameters represent the following:

• 1 = indicating that the Progress Bar must be initialized.

• 5 = This is the maximum number of steps of Queries, other macros, or Functions that we are going to run.

• PB2 = The Name of the Progress Bar Control drawn on the Form.

12. 'ProgMeter2()' This function must be called after each query execution step in the Macro to update the Progress Bar. However, subsequent calls for updating the Progress Meter require only a single parameter: ProgMeter2(0), where 0 indicates that the meter should simply advance to the next step.

    To implement this:

    • Insert a RunCode action after each query in your Macro.

    • In each inserted line, set the function call as ProgMeter2(0).

    At the end of the Macro, add one final RunCode action that calls ProgMeter2(2). The parameter 2 signals the function to complete the process and turn off the Progress Bar.

    Important: If you later add more steps to the Macro but forget to update the initialization value (i.e., the second parameter in ProgMeter2(1, 5) where 5 is the total number of expected steps), the program will stop updating the meter once it reaches the original maximum step count and will remain idle until it receives the terminating parameter 2.

    ---

    Save the Spl_Report Macro after the above changes.

    The Progress Meter2 Function.

    Copy and paste the following VB Code into a Global Module in a Global Module and save it.

    Public Function ProgMeter2(ByVal xswitch As Integer, Optional ByVal Maxval As Integer, Optional ByVal strCtrl As String)
    '----------------------------------------------------------
    'Program : Progress Bar Demo2
    'Author  : a.p.r. pillai
    'Date    : 02/01/2008
    '----------------------------------------------------------
    Static mtrCtrl As Control, i As Integer, xmax As Integer
    Static lbl As Label, frm As Form
    Dim position As Integer, xtime As Date
    
    On Error GoTo ProgMeter2_Err
    
    If xswitch = 1 Then
     'init control
        Set frm = Screen.ActiveForm
        Set mtrCtrl = frm.Controls(strCtrl)
        Set lbl = frm.Controls("lblstatus")
        mtrCtrl.Visible = True
        lbl.Visible = True
        xmax = Maxval
        i = 0
        DoEvents
    ElseIf xswitch = 0 Then
        i = i + 1
        if i > xmax then
              goto ProgMeter2_Exit
        end if
        position = i * (100 / xmax)
        mtrCtrl.Value = position
        DoEvents
    ElseIf  xswitch = 2 Then
        mtrCtrl.Visible = False
        lbl.Visible = False
    
        Set mtrCtrl = Nothing
        i = 0
        xmax = 0
        Exit Function
    End If
    
    ProgMeter2_Exit:
    Exit Function
    
    ProgMeter2_Err:
    MsgBox Err.Description, , "ProgMeter2"
    Resume ProgMeter2_Exit
    End Function

    The Program Logic is the same as that of SysCmd(), which we used for updating the Status Bar in our earlier example. The only difference is that our program updates the Progress Bar on a Form.

    Note: The built-in Function SysCmd() has other implementations too, with a different set of Parameters.

    Open the ProgressMeter Form in Normal View. Click on the [Process Orders] Command Button to run the process steps in the Spl_Report Macro.

    The Progress Bar will now appear on the Form with the message 'Working...' on the left-side label.

    The image of the sample run is given below.

    

    You can implement this method on any Form, such as your Control Screen or a dedicated report parameter Form. When calling the ProgMeter2() function, be sure to use the actual name of the Progress Bar control placed on your Form, replacing "PB2" (in our example) with your control's name. For instance:

    vba
    ProgMeter2(1, 5, "MyProgressBar")
    

    • The first parameter (1) Initializes the Progress Bar.

    • The second parameter (5) specifies the total number of steps.

    • The third parameter ("MyProgressBar") is the name of your custom Progress Bar control.

    Subsequent calls to update the Progress Bar require only a single parameter:

    vba
    ProgMeter2(0)
    

    And at the end of the process, use:

    vba
    ProgMeter2(2)
    

    This final call closes and resets the Progress Bar.

    Preventing Pitfalls

    Further enhancements to the Form design and underlying code are recommended to prevent unintended user interactions—such as accidentally closing the Form or switching focus to another task—while the process is running. If the Form becomes inactive during execution, it may lead to runtime errors or interrupt the process.

    To ensure stability and user focus:

    • Disable the Close button on the Form.

    • Set the Form’s Modal and Pop-Up properties to Yes.

    • Optionally, disable other navigation controls and prevent focus changes during execution.

    As mentioned earlier, the ProgMeter2() Function can also be effectively used in VBA routines that perform record-level updates on Tables, providing a clear visual indicator of processing progress and improving user experience during lengthy operations.

    Download the Demo Database.

    
    Download ProgressMeter2007.zip
    

    
    Download ProgressMeter.zip
    

    
    

    • Progress Bar on Form
    • Progress Counter
    • Progress Meter
    • Reminder Pop Up
    • MS-Access & Graph Charts-2