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
Open the 'ProgressMeter' Form in Design View.
Select the ActiveX Control Option from the Insert Menu.
Select Microsoft Progress Bar Control, Version 6.0 from the displayed list.
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.
Select the Progress Bar Control (if you have deselected it) and display the Property Sheet (View --> Properties).
Need to change the following Property Values as shown below:
- Name = PB2
- Visible = False
Create a label on the left side of the ProgressBar. Display its Property Sheet and change the following values:
- Name = lblStatus
- Visible = False
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.
Save the Form after the changes.
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.
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.
Hi,
ReplyDeletethx 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
Thanks for the Tip Olly.
ReplyDeleteRegards,
a.p.r. pillai
[...] LEARN MS-ACCESS TIPS AND TRICKS - Progress Bar οn Form [...]
ReplyDelete[...] LEARN MS-ACCESS TIPS AND TRICKS - Progress Bar on Form [...]
ReplyDelete[...] LEARN MS-ACCESS TIPS AND TRICKS - Progress Bar οn Form [...]
ReplyDeleteMr. P:illali;
ReplyDeletedo 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 !
Hi Mmadani,
ReplyDeleteYou can download Access2007 version of the Progress Meter from the second download link (http://www.msaccesstips.com/downloads/2008/01/ProgressMeter2007.zip) given above.
[...] 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