I know this one has been hashed over a few times, but all of the examples I’ve seen are either really clunky to use, or rely on an ActiveX control. I needed to create a progress bar that was native to Access and easy to use.
Start with creating a blank unbound form.
Go to design mode and resize the detail area and add a rectangle named boxProgress and a text box named txtStatus as shown below. Leave just a little bit of space around your rectangle. Set the “Back Style” property of the text box to “Transparent”. You’ll also need to make sure that the Form Property “Pop Up” is set to “Yes”. Make sure the control names are set properly, the code relies on those names.
Ok. Are you ready for the hard part? You need to adjust the size of the frame of the form to display just the right amount of the detail area. It needs to actually be smaller than the detail area when you’re in design mode. This will take a few tries to get right. Just keep at it, you’ll get it right eventually. It will look something like this in design mode.
And this in form view:
Now that we have a form to work with, we’ll need to add some code behind it. This whole thing only works because Access Forms are glorified class modules. We can do things like add properties and call new instances of forms. All of the code I will share with you is based on this fact. Go ahead and paste the code below into the vbe under your new progress bar.
Option Compare Database Option Explicit ' *************************************************************** ' * Class: Form_ProgressBar * ' * Popup progress bar * ' * * ' * Author: Christopher J. McClellan * ' * Published under Creative Commons Attribution-Share Alike * ' * http://creativecommons.org/licenses/by-sa/3.0/ * ' * You are free to change, distribute, and pretty much do * ' * whatever you like with the code, but you must give credit * ' * to the original author and publish any derivitive of this * ' * code under the same license. * ' *************************************************************** Private Const MaxBoxWidth As Long = 7200 'maximun boxProgress width Public Enum ePBarModeType PBarMode_Percent = 0 PBarMode_Executing = 1 End Enum Private mMode As ePBarModeType Private mCurrentProgress As Long Private mSteps As Long Public Property Get PercentComplete() As Double 'read only PercentComplete = mCurrentProgress / mSteps * 100 End Property Public Property Let Mode(PBarMode As ePBarModeType) mMode = PBarMode End Property Public Property Get Mode() As ePBarModeType Mode = mMode End Property Public Property Let CurrentProgress(lng As Long) ' Updating the CurrentProgress property updates the status of the Progress Bar mCurrentProgress = lng ' format #0 makes a 1 or 2 digit number without decimals If mMode = PBarMode_Percent Then 'format "#0" gives a 1 or 2 digit integer Me.txtStatus = Format(Me.PercentComplete, "#0") & " % Complete" ElseIf mMode = PBarMode_Executing Then Me.txtStatus = "Executing..." End If ' boxProgress.Width = a percentage of maximum box width Me.boxProgress.Width = (mCurrentProgress / mSteps) * MaxBoxWidth Me.Repaint DoEvents End Property Public Property Get CurrentProgress() As Long ' current step of process CurrentProgress = mCurrentProgress End Property Property Let steps(lng As Long) ' total number of steps to process mSteps = lng End Property Public Sub init(steps As Long, Mode As ePBarModeType, Optional strCaption As String = "Loading...") ' initializes values for progress bar Me.Mode = Mode Me.Caption = strCaption mCurrentProgress = 0 mSteps = steps Me.txtStatus = "Ready" Me.boxProgress.Width = 0 Me.Visible = True End Sub
Now let’s create a new module and use our new progress bar. We’ll create a new instance of our progress bar like we would any other class object. Set pbar = New Form_ProgressBar (or whatever you happened to name your form). In the first example, we’ll use it in a simple for loop.
</pre> Private Sub exampleCall1() ' example call for using progress bar with a looping process Dim pbar As Form_ProgressBar Dim i As Long Dim steps As Long steps = 100000 ' create new instance of Progress Bar Set pbar = New Form_ProgressBar With pbar ' #of steps, Mode, Caption .init steps, PBarMode_Percent, "Hey, I'm working here!" For i = 1 To steps ' do something in a loop ' update progress .CurrentProgress = i Next i End With Set pbar = Nothing End Sub
Running example call 1 will result in a progress bar that looks like this.
Private Sub exampleCall2() ' example call for using progress bar with an adodb.command Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim prm As ADODB.Parameter With cn .Provider = "Microsoft.Access.OLEDB.10.0" .Properties("Data Provider").Value = "SQLOLEDB" .Properties("Data Source").Value = "serverName" .Properties("User ID").Value = "userName" .Properties("Password").Value = "password" .Properties("Initial Catalog").Value = "databaseName" .CommandTimeout = 500 .ConnectionTimeout = 500 .Open End With With cmd .ActiveConnection = cn .CommandType = adCmdStoredProc .CommandTimeout = 0 .CommandText = "dbo.uspSomeStoredProcedure" End With 'must execute command asynchronously in order to show progress bar while remote command executes. ' otherwise, the vba code freezes until the command completes. cmd.Execute , , adAsyncExecute Dim pbar As New Form_ProgressBar Dim i As Long pbar.init 10000, PBarMode_Executing, "" Do While cmd.STATE = adStateExecuting For i = 0 To 10000 pbar.CurrentProgress = i Next i Loop Set pbar = Nothing DoCmd.Hourglass False End Sub
Example call 2 will result in a progress bar that infinitely loops while displaying an “Executing…” message. Enjoy.
UPDATE:
I’ve posted the actual form and code to Google Drive. You can now download the file.
#1 by Daniel Wishart on June 10, 2014 - 12:25 pm
I get a compile error when running the sub. It seems the ADODB.Command variable is causing the problem. Any ideas?
LikeLike
#2 by Christopher J. McClellan on June 10, 2014 - 1:58 pm
You need to add a reference to the Microsoft ActiveX Data Objects 6.1 Library Daniel. I’m not looking at the code right this second, but the progress bar doesn’t rely on it. Just the example call does. You could also just remove the example call from the code.
LikeLike
#3 by Rob Hoogland (@robhoogland) on May 16, 2015 - 8:58 pm
Hi Christopher, it took me some time to get it to work in my existing module in my database. But it look great. I’m still a noob with VBA, but is it possible to let the progress bar decrease to the right side? Then with the color of ProgressBar set to white you can put a image behind it to simulate a multicolor progress bar (window 7 sty
LikeLike
#4 by Christopher J. McClellan on May 16, 2015 - 10:27 pm
I’m sure it’s possible. Yes. Been a while since I looked at that code though. You would have to adjust both it’s width and position (Left) to make it scroll from the right I believe. Mind if I ask why you’d want it to do that? I’ve never seen a progress bar go from right to left. Or do you mean go from 100% to 0? In that case, I think you could just start at max width and decrease it each time you set the current progress. A bit of a hack, but should work ok. Comment your code well or it probably won’t make sense 6 months from now.
LikeLike
#5 by Rob Hoogland (@robhoogland) on May 18, 2015 - 4:39 am
Hi Christopher, just an hour after my comment I found the solution myself. I made the following changes to your code:
‘ boxProgress.Width = a percentage of maximum box width
Me.boxProgress.Left = (mCurrentProgress / mSteps) * MaxBoxWidth
Me.boxProgress.Width = MaxBoxWidth – ((mCurrentProgress / mSteps) * MaxBoxWidth).
The reason I asked was because I saw the progress bar in Outlook which isn’t just plain green but has a sort of glow/shine over it. I just wanted it to reproduce that.
With a white progress bar decreasing from right to left, with a image of the Outlook progress bar behind the boxProgressBar, I can visually achieve that.
Ofcourse there would be a simpler way, but for now I’m happy.
LikeLike
#6 by Rob Hoogland (@robhoogland) on May 18, 2015 - 6:56 am
Or just use name the image boxProgress and stick with the original code. That also does the trick 🙂
LikeLike
#7 by Christopher J. McClellan on May 18, 2015 - 9:51 am
Happy to hear you worked it out.
LikeLike
#8 by Robert Shaw on June 3, 2015 - 2:17 pm
I have a number of pass-through queries to a SQLServer that can take several minutes. Although I turn on and off the Hourglass, I like the idea of a continuous loop progress bar. I use DAO and prefer not to change to ADO on all my routines. Since there is not a Connection.State equivalent in DAO, any thoughts on how to modify the Do While cmd.STATE = adStateExecuting to a DAO compatible process?
Thank you.
LikeLike
#9 by Christopher J. McClellan on June 3, 2015 - 3:11 pm
You know, that’s a really good question. Honestly, I really just don’t know enough about DAO to say. What I can tell you is that my implementation of the continuous loop hinges on executing the command object asynchronously. I’d start with looking into if DAO is capable of doing the same. If it can’t execute async, the pass through will block the UI and it won’t work. If you can execute async though, you should be able to a similar pattern to ExampleCall2.
LikeLike
#10 by Robert Shaw on June 3, 2015 - 6:25 pm
There are DAO method/properties dbRunAsync and .StillExecuting which seem viable options, but in 2010 the ODBCDirect object is no longer supported for external databases. MSDN states to use ADO. QueryDef.Execute supports dbRunAsync but does not support SELECT queries only action queries. I seem to be back to the starting place.
Thanks for your comments.
LikeLike
#11 by Christopher J. McClellan on June 3, 2015 - 7:58 pm
Sounds like it. If you’re already using a passthrough query, it shouldn’t be hard to convert to ADODB, depending on your needs. Some parts of Access just doesn’t function well on ADODB to be honest about it.
LikeLike
#12 by Wim Van Geyt on October 15, 2015 - 9:41 am
Hi, I am getting an error on the custom type ePBarModeType? Where do you define this type?
LikeLike
#13 by Christopher J. McClellan on October 15, 2015 - 10:01 am
It gets defined just after the license header.
LikeLike
#14 by George Karagiannakis on November 23, 2015 - 11:17 am
When you say “Go ahead and paste the code below into the vbe under your new progress bar.” what do you mean? Where is exactly this? Insert the code as a new Class in VB Editor?
LikeLike
#15 by Christopher J. McClellan on November 23, 2015 - 12:24 pm
In the form’s code behind.
LikeLike
#16 by George Karagiannakis on November 23, 2015 - 5:25 pm
Thank you very Christopher for your code and your immediate response. I have a search form with a text and a button. The user insert keyword in the text box and when the button is clicked a query is running through the onClick event in Access.
I would like to ask you how can I include the code from example 1 code into this event. I tried to put the sql query into the loop after the for statement but it was very slow and I think it was not correct.
LikeLike
#17 by Christopher J. McClellan on November 23, 2015 - 6:11 pm
If you want to show “progress” on a query, you’ll need to use example call 2, but it only works with connecting to an ADODB provider that supports async. Note that it won’t show actual progress, but just an indication that the program is doing something.
LikeLike
#18 by Secretary JJNH on August 16, 2016 - 12:28 am
Thank you very much. Your code worked perfectly and was a great aid to my project. Thanks a Ton.
LikeLiked by 1 person
#19 by Christopher J. McClellan on August 16, 2016 - 12:42 am
I’m glad you found it useful!
LikeLike
#20 by Mario Mirandola on April 6, 2017 - 8:40 pm
This is exactly what I have been looking for for quite a while to monitor the progress of a pretty massive, time consuming macro that I have. I have to apologize that I am very limited in my VBA/Coding skills. How do we link what you gave us to the macro or query we want to utilize this on?
LikeLike
#21 by Christopher J. McClellan on April 9, 2017 - 11:14 pm
That depends on what kind of query you have/what kind of work you’re doing in the background. The progress bar supports two different modes. Which you use depends on how you execute the query.
LikeLike
#22 by Mario Mirandola on April 11, 2017 - 7:46 pm
I have a macro that calls a series of update queries that update numerous tables. To make this as simple as possible how do I set it up to monitor a single update query that is called from a macro?
LikeLike
#23 by Tony Kelly on August 18, 2017 - 5:38 pm
Christopher — this is awesome! Thank you so much. Your guide was very helpful.
However, I am stuck. I am connecting to an external OBDC database through access, and am running a set of queries. I have a macro which calls a query, and once the data is set it alerts the user the first query is done, and repeats that process 5 times (Calling 5 queries, with each one recursively calling those before it).
It takes up to 20 minutes to run and I would love to show a status bar. How can I do that using Example 2?
Thank you!
LikeLike
#24 by Christopher J. McClellan on August 20, 2017 - 10:56 am
Hi Tony. Example 2 isn’t really a progress bar so much as it’s a busy indicator. For your usecase, the best I think you could do is update the progress bar where you’re currently alerting the user right now.
LikeLike
#25 by Tony Kelly on August 21, 2017 - 11:49 am
Hi, Chris. Thanks a lot for your very quick feedback and your reply on a somewhat older thread! I guess I am just confused logically here…but maybe I can ask you for your input on your tool. I would love to use (and of course properly cite and give credit to) you and your site for helping with this progress bar issue I have.
My macro currently calls 3 queries (the third being the largest). I would like it to update the pbar at each alert (10% after push button, 10% after first query, 20% after second query, 30% during third query, 30% at end of third query). I’m just calling them with:
DoCmd.OpenQuery and DoEvents so that they are called sequentially. Do you have advice on how to update the status bar at each of those levels WITHOUT using a loop?
Thanks!
Tony
LikeLike
#26 by Marc Weintraub on April 16, 2018 - 5:37 pm
Hi, Christopher. I have implemented your code and it is working well. I tried to make a small “improvement” where I could update the form’s caption with new text as each step in my process began. It did not work.
I saved the form you provided into my daabase as “fProgressBar”.
Modifying: “Public Property Let CurrentProgress(lng As Long)”
to: “Public Property Let CurrentProgress(lng As Long, strCaption As String)”
resulted in a run-time error message complaining of an incorrect number of parameters. I had changed the call from: “pbar.CurrentProgress = 1” to: “pbar.CurrentProgress 1, “Creating a temp table”” but that did not resolve the issue.
With that failing, I restored the prior syntax on all affected statements and inserted “Form_fProgressBar.Caption = “Creating a temp table”” before the call to “pbar.CurrentProgress = x” but the caption did not update.
Your code is leaps and bounds ahead of my VBA prowess. I would greatly appreciate any suggestions you can offer or explanations of why I can’t do it.
BTW, the form covers approximately 2/3 of the screen when it appears. The progress bar appears near the top. Is that what I should expect?
Thanks in advance,
Marc
LikeLike
#27 by André Niquet on May 9, 2018 - 6:27 pm
Wow…. it is working like a charm, thank you for sharing
LikeLike
#28 by Xavi A on April 11, 2019 - 3:56 pm
Hi
When running the example 1, I get an error of “Compile error: Method or data member not found”
I am probably missing a library. Do you happen to know which one? I am using Access 2010.
I did add the Microsoft ActiveX Data Objects 6.1 Library.
Private Sub exampleCall1()
‘ example call for using progress bar with a looping process
Dim pbar As Form_ProgressBar
Dim i As Long
Dim steps As Long
steps = 100000
‘ create new instance of Progress Bar
Set pbar = New Form_ProgressBar
With pbar
‘ #of steps, Mode, Caption
.init steps, PBarMode_Percent, “Hey, I’m working here!” <——- The .init gives the error
For i = 1 To steps
' do something in a loop
' update progress
.CurrentProgress = i
Next i
End With
Set pbar = Nothing
End Sub
Thank You
LikeLike
#29 by Christopher J. McClellan on April 21, 2019 - 12:04 pm
Hey Xavi. I’ve not worked with Access for a number of years, so your best shot is probably to post a question on StackOverflow.
I’m sorry that you’re having trouble getting this to work.
LikeLike
#30 by Eugenio Caldentey on June 6, 2020 - 5:48 pm
It worked for me with no problem (of course some adjustments to the form setting …. but great solution! Tanks man!
LikeLike
#31 by Admin on May 3, 2021 - 11:03 am
Thank you so much. Short code that is of great help in my processing of records.
LikeLiked by 1 person