Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.

NB: This method for Macros was found working in MS Access 2000 only. When tried in MS-Access 2003 it totally ignores updating the Progress Bar Control, even with the database Formats MS-Access 2000, 2002, and 2003. If any readers found a solution to this problem, please share it with me too.

However, you can use this method with our earlier Program replacing the sysCmd() lines for transaction-level processing, to view the Progress Bar on your own Form rather than on the Status Bar.

The Form Design

  1. Open the ProgressMeter Form in Design View.
  2. Select the ActiveX Control Option from 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. You have to do some resizing 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. Change the following Property Values are 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 Tools Menu. Uncheck the Status Bar under the Show Option group on 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 we will 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. The Function must be called after every Query running step to update the Progress Bar. But, subsequent calls to the function ProgMeter2(0) need only 0 (zero) as a Parameter indicating that it is the Progress Meter's updating step.
  13. Insert one row each after every Query on the Macro, Copy and paste the RunCode macro line on the inserted rows calling the Function ProgMeter2(0)
  14. At the end of the Macro steps add one more line to call the Function ProgMeter2(2), with the parameter value 2 indicating that the work is over and to turn off the ProgressBar.
  15. If you have added more steps later in the Macro and forgot to modify the initializing value (second parameter 5) the program will ignore further updating calls and wait for the terminating parameter 2 for closing the Progress Bar.
  16. Save the Spl_Report Macro after the above changes.

    The Progress Meter2 Function

  17. Copy and Paste the following VB Code into a Global Module in your Project 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 of SysCmd() we have used for updating the Status Bar in our earlier example. The only difference is 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.

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

The Progress Bar will now show up 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, like on your Control Screen or on a separate report running parameter Form. When you call the Function ProgMeter2(), use the Name that you have given to the Progress Bar Control as the third parameter to the Function in place of PB2, which we have used for our example: ProgMeter2(1, 5, "PB2"). The subsequent call to the Functions needs only one parameter value 0 for updating the control at each step and 2 to close the Progress Bar at the end.

Preventing Pitfalls

Further improvements to the design of the Form and the Code are required to prevent users from closing the Form by accident or straying away from the Form for doing something else making the Form inactive etc. In such situations, the program may run into Errors.

As I have mentioned at the beginning you can use this function for your VB Routines that update Table at the record level.

Download the Demo Database.


Share:

8 comments:

  1. Hi,

    thx for this great Tutorial. It helped me a lot.

    For usage in MS Access 2003 try this some code like this:

    Private sub workaround_2003
    Dim tst As Variant
    tst = ProgMeter2(1, 5, "PB2")
    tst = ProgMeter2(0)
    Sleep (2000)
    tst = ProgMeter2(0)
    ...
    tst = ProgMeter2(2)
    Sleep (500)
    End Sub

    It works like charm without using macro's.

    Regards Olly

    ReplyDelete
  2. Thanks for the Tip Olly.

    Regards,
    a.p.r. pillai

    ReplyDelete
  3. [...] LEARN MS-ACCESS TIPS AND TRICKS - Progress Bar on Form [...]

    ReplyDelete
  4. [...] LEARN MS-ACCESS TIPS AND TRICKS - Progress Bar &#959n Form [...]

    ReplyDelete
  5. Mr. P:illali;

    do you have one fr 2007 where the progress bar updates on a form. When I try these. They do not work in 2007 or 2010. any help would be apprecaited.

    Thank you so much !

    ReplyDelete
  6. Hi Mmadani,

    You can download Access2007 version of the Progress Meter from the second download link (http://www.msaccesstips.com/downloads/2008/01/ProgressMeter2007.zip) given above.

    ReplyDelete
  7. [...] See if this helps http://msaccesstips.com/2008/01/progress-bar-on-form/ Alan __________________ If I helped you solve your issue, click on the scales in the upper [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code