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.
I’ve posted the actual form and code to Google Drive. You can now download the file.