Access vba : to display time left when code is running

Question

Hi,

I have a form with a few buttons which execute code when pressed like running validations on the database. some of these code could could run for a few minutes so is there any way to show the time remaining or a message to display the % of process completed.

or pop out a message when code evaluation starts and the message should disappear once code running is completed

thanks

Answer 1

In order to do this the "normal" way, you'd need to run your validation in another thread and have it report its progress back to the UI thread. However, I don't believe VBA supports any kind of multithreading.

If your validation routines involve a loop, or even just many separate discrete operations, you can try inserting a DoEvents statement in between loop iterations (or operations), and then have your progress display updated periodically (say, in an Application_OnTime event handler).

Answer 2

I usually have a form I name frmProgress or whatever, with a cancel button and a label for displaying a status message. Then embedded in the form code I have a boolean called bCancel, and when you hit the cancel button it simply sets bCancel as true.

Also in this code I have a routine called ShowPercDone( Idx , NumIdc ) where Idx is the step the code is on, and NumIdc is the number of steps the code will take (assuming each step takes the same amount of time). This works well when I'm running through a for loop, but basically any time I want to display a status update I just call the routine in the form with my message, which I should add runs the doevents command for me.

So that's how the status form works. In the macro I run, I start out by just calling frmProgress.show (0) so that it lets you click the cancel button. Then in my loop when I update the status message I then check frmProgress.bCancel and if it's true I exit out of the macro.

Hope that helps.

Answer 3

What you are probably looking for is a "Progress Bar".

I've used the Microsoft ProgressBar control (you can find it under Insert->ActiveX Control), and it's not that hard to use. Just set the value of it to a percentage (as an integer, not a decimal).

'foo, being the ProgressBar
me.foo = 70 '70%

There is some good info here on another method: http://www.granite.ab.ca/access/progressbar.htm

Answer 4

Finally to be simple i decided to use the method given here

http://oreilly.com/pub/h/3330#code