Add "Progress Meter" to a working VBA routine

[EEH_Help]

Good afternoon:

In a different post (https://www.access-programmers.co.u...-allow-listboxs-multi-select-extended.318906/), I received some fantastic help from expert **MajP**.

In his final solution, MajP included a progress meter. I find this progress meter so great that I now would like to include in other databases which require some processing time.

Please find attached 3 DBs... I'll recap the procedures below:

*** "01 Compare Tables" *******
- This is the final version (with limited data) provided by MajP.
- Upon opening the file, form "F01_MainMenu" automatically opens.
- Select "AICRAFT" from listbox.
- The click "View Change Log".
- At this point, the **Progress Meter** kicks in and provides visual status feedback to the user.

Added objects:
- Form "frmMeter"
- Modules "clsDualMeter" and "clsMeter"

The above objects were somehow tied into the module(s) "modCompareTables" and/or "modGlobalCode". No reference to the objects were made in forms' VBA.

Again, this progress meter works great... ** no changes are required for this version! **


*** "02A Generate Products v01" *******
- This DB includes a completely different routine... it is totally independent from file "01 Compare Tables".
- Upon opening the file, form "F01_MainMenu" automatically opens.
- Click on command button "Generate Master PK Table".
- Within approximately 5 seconds, message box pops up indicating that 100 records were added.
- This version also works great and does NOT require any modification whatsover.


*** "02B Generate Products v02" *******
- This DB is an exact copy of "02A Generate Products v01".
- In this version though, I have included the progress meter objects: "frmMeter", "clsDualMeter", "clsMeter".
- Since this DB is a replica of v01, it naturally wors in the same fashion.

Here's what I need some help with (in version "02B Generate Products v02"):
- Modify the VBA (forms or modules) so that the progress meter "kicks in" once I click "Generate Master PK Table" (on form "F01_MainMenu).
- Please note that both versions (v01 AND v02) only includes 100 sample records; thus, this routine processes very quickly (~4 seconds).
- However, in my actual solution though, the "Generate Products PK Table" runs approximately for 2 to 3 minutes. Therefore, this progress meter will come in very handy.

My question:
- What modifications need to be made to "02B Generate Products v02 so that I get the progress meter working as well?

Thank you,
EEH

 

[MajP]

I did not develop the progress meter code, but have used this in several DBs. Here is the full demo. If you search this forum there are others.
@isladogs has one here

Progress Bar - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

 

[EEH_Help]

MajP -- thank you... copy all.

The attached sample file is very helpful. I'll try to integrate in the other process.

Thanks again for both the sample file and URL.

Cheers!

 

[isladogs]

@MajP
Just noticed your form shows the 'dotted square bug' in the top left corner when you click & hold the close button
It was described by Helen Feddema a few years ago & I mentioned it in this recent post:
"invisible" box on form in layout view | Access World Forums (access-programmers.co.uk)

 

[MajP]

That example is not mine and seems pretty old since it is in an.mdb. it would be simple to update with a more modern form.

 

[EEH_Help]

Copy... thank you, MajP. I'll with lines will have to be integrated in my DB.

Cheers!

 

[Frothingslosh]

I did not develop the progress meter code, but have used this in several DBs. Here is the full demo. If you search this forum there are others.
@isladogs has one here

Progress Bar - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

You know, I really should get around to updating that thing to .accdb files one of these days.

 

[isladogs]

Unfortunately, the link to my progress bar in posts #2 & #7 no longer works

Here is the updated link to my progress bar on my new website:

Progress Bar

This is a simple but effective progress bar for use in Access forms. Several different versions are provided based on a timer or a series of events. The progress bar can use a solid bar, image or gradient fill.

www.isladogs.co.uk

 

[Frothingslosh]

Interesting - that doesn't look like my code, but MeterWithForms_v1.02.mdb was very much the last version of mine I posted to the Code Library! It looks like I conflated MajP's attachment with his link to your site.

Well, I should definitely update the version in the code library at some point. It was uploaded as A2003 for compatibility purposes, but that's long since dead by now.

I do like the multi-colored bars you used in your example images.

 

[Pat Hartman]

Nice sample Colin. Is it really useful for time though? I've only used progress bars when I know how many records need to be processed. I guess you could count the number of records and guestimate time per record to come up with the total number of points. The sample is hardcoded at 50.

Also, rather than hard-coding the form, why not pass in a reference?

Public Sub SetupProgressBar(frm As Form)

Then call with:

Call SetupProgressBar(Me)

 

[isladogs]

Nice sample Colin. Is it really useful for time though? I've only used progress bars when I know how many records need to be processed. I guess you could count the number of records and guestimate time per record to come up with the total number of points. The sample is hardcoded at 50.

Also, rather than hard-coding the form, why not pass in a reference?

Public Sub SetupProgressBar(frm As Form)

Then call with:

Call SetupProgressBar(Me)

The timer was just used as an example to make the example app simpler. As the web article states:

Normally the bar would be used to indicate the progress of a lengthy procedure containing many separate steps e.g. SQL statements or queries or a repeated code loop

However, for the purposes of the example databases, a form timer event is used to show progress

The number of steps are set in the calling form. 50 is just used as a default in case that info is omitted

Good point about using a form reference
I wrote the original progress bar code in that article over 15 years ago and long ago changed it to use a form reference exactly as you suggested.
I do exactly the same thing in several of my example apps e.g. for Automatic Form Resizing I use ResizeForm Me

I will update the article accordingly!
Thanks for the prompt

 

[Isaac]

@EEH_Help
I use an extremely simple method for progress bars where the # of iterations is known (which IMO is the only context progress bars really work reliably anyway)

See this post - there are some (trivial) aspects that are Excel vba, but all of the basic components are the same in Access.

What I think is good about this is you can design it in 5 min using your own code that you understand...Which is a goal to strive for that if it doesn't mean much to you now, it will eventually..

Progress Bar

Good Morning All, I have a "Simple" problem that I'm struggling to solve. My code loops through multiple txt files in a specific fold and converts them to excel files. I'm developing a piece of code to show the user the Progress of conversion of each file. What do I need to change to make it...

www.access-programmers.co.uk

 

[isladogs]

Many thanks to @Pat Hartman for prompting me to update the progress bar examples & code on my website

Progress Bar

This is a simple but effective progress bar for use in Access forms. Several different versions are provided based on a timer or a series of events. The progress bar can use a solid bar, image or gradient fill.

www.isladogs.co.uk

The modified functions all use a form reference so are coded as: SetupProgressBar Me, UpdateProgressBar Me, HideProgressBar Me

The example app on the website now has 4 versions:
a) The original 3 versions using a timer with a solid bar, image fill & gradient fill
b) I have also added an example based on the standard approach running a series of events.
See attached

I have also posted this example app to a new thread in the code repository

Progress Bar

Attached is an updated version of my progress bar app. The example app contains 4 forms with different variations of the progress bar: 1. using a solid bar with progress based on a series of events (update queries). 2. using a solid bar with progress done using a timer event. 3...

www.access-programmers.co.uk

 

[Pat Hartman]

You're welcome