Progress bar that is constantly updating

[Rob]

I have a db query that take a long time to run. I was wondering if I
could have a progress bar updating while the query is running.
Essentially I would estimate how long the query would take and set a
loop to take approximately the same time.

I have used progress bars in the past.

The part that I do not know how to do (or if it is possible) is to
have the progress bar loop running while other code is simultaneously
running.

Any help is greatly appreciated!
Cheers!

Rob

 

[Bob Phillips]

Unless you have some interrupts in the query where you can update the PB
from, it won't be very effective.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rob]

I agree that it will not be very effective, but I just wanted to have
something moving so that the user knows that the query is still
chugging.

 

[Bob Phillips]

No, by not effective, I mean it won't be moving. You need an interrupt to
move the PB along.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[The Dude]

You have several ways to show the progression of your coding:

- display a message in the statusbar with the application.statusbar function.
- display a userform that will show the progressbar.

Obviously the first one is the easiest. For the second one, you must first
install an extension in your userform's toolbar.
First, create a userform. Right-click the toolbar (where you have the
controls) and add the 'Microsoft Progressbar Control'. Design your userform
as you like, with a text label for instance to display messages or only the
progressbar.

Then, make sure that the Userform is NOT MODAL (Showmodal = false) so that
it does not halt the code.
You have two ways to update the userform: create a function that will group
the modification and call the function regularly, or insert the code in your
sub.

an example of code directly in your sub :
userform.progressbar1 = 50 'to show 50%
doevents 'let the system update the display (you loose very little
time...)

OR

function progress_bar_update(progress, text)

userform.progressbar1 = progress
userform.text1 = text
doevents

end function

then you can call this function by for instance progress_bar_update(50, "My
text")

 

[pgarcia]

Dude, do you have an example of this? I can't seem to make it work. It
"debugs" out.
Thanks

 

[The Dude]

An example... hmm...

I will give you how I did proceed and if it does not work then feel free to
reply...

First you create a form, then you add a progressbar control: for that you
need to click on the lowest button to the right of the tools bar. It will
then show a list of controls and you must scroll down to the Microsoft
Progressbar Control. I use Access 2003 and have the PB Control 6.0

Then you can add whatever control to test it, like a button. If you double
click the progressbar you have the different settings you can change.
So let's say you create a button, create the code that says something like:
Me.ProgressBar0 = 75
for 75 percent.

And so on. During the code you can add any value for it, formulas and so on...

Let me know how it goes.

 

[pgarcia]

Thanks for write back. I got it now. I just had to play with it a bit.