Access VBA, adding Progress Bar in code to allow execution.

I have tried several online tutorials on creating a Progress Bar when executing code, but I haven't been successful in getting it to work. Does anyone have a working solution for either using built-in Progress Bar or maybe a popup Progress Bar?
toalexsandrAsked:
Who is Participating?
 
Bill PrewCommented:
Have you tried the built in status bar based one.  It's pretty small and less obvious but can be adequate in some cases.  Here is a small sampole procedure to test, and then notice the right side of the status bar in the main window when running, it runs for 10 seconds.

Sub ProgressMeter()
   Dim i As Integer
   
   DoCmd.Hourglass (True)
   
   SysCmd acSysCmdInitMeter, "Working...", 10
 
   For i = 1 To 10
      SysCmd acSysCmdUpdateMeter, i
      Wait 1
   Next i
 
   SysCmd acSysCmdRemoveMeter
         
   DoCmd.Hourglass (False)
         
End Sub

Sub Wait(s As Integer)
   Dim t As Long
   t = Timer()
   Do
      DoEvents
   Loop While (Timer() < t + s)
 End Sub

Open in new window

~bp
0
 
toalexsandrAuthor Commented:
Works actually very well. Will it be always for 10 seconds? Can it be adjusted and allow the system to use its processing timing?
0
 
Bill PrewCommented:
It is driven off a counter that the code controls.  Notice that I set the range to end at 10, and then called the "update" of the meter with number 1, 2, ..., 10 to let it show updates.

What are you trying to have going on while the progress bar is active, are you waiting for the result of a long query, or do you have application logic in VBA that is processing? If it's just a long query that is running then that isn't going to work well for this, since you have no ability to execute VBA code while it is running.

Need to know a little bit more about how and when you want to use this...

~bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
toalexsandrAuthor Commented:
I want to use it when executing VBA for Backing-up DB or any activated button.
0
 
Bill PrewCommented:
If it's something where you have VBA code executing during the time you want to display and update it then that can work.

If it's during the time a single statement is executing, like backup up the database I suspect, then you cannot use it effectively there, since you have no VBA code executing while that single statement is executing.

I have some techniques using a form that responds to timer events, but not sure if they would fire while the backup is running.  But even with that approach the problem is you don't know how long the backup will take, so you can't estimate what percentage has been done at any point in time, making updates to the bar not possible for any useful estimate of % complete.

~bp
0
 
Dale FyeCommented:
I've got an article on progress bars located here with a sample form and code.
0
 
toalexsandrAuthor Commented:
Thank you for all your help. I will review the article once more because it's very informative but for now, Bills code works perfectly for me. Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.