Access has a built-in status bar with progress meter. It's the one you see at the bottom of your screen when you execute a long-running query. We can use this status bar for our own purposes, but to do so directly requires using the Application.SysCmd function.
There are some peculiarities about updating the status bar. For example, if you call
SysCmd acSysCmdSetStatus to update the status bar text, it wipes out the progress bar. Also, there's no way to increment the progress bar and update the accompanying text in a single call.
These are not insurmountable challenges, as the code below shows, but you'll never remember all the idiosyncracies. And someday you'll come along to make a small change to the code and foul everything up because the whole process is unintuitive.
SysCmd acSysCmdClearStatus SysCmd acSysCmdInitMeter, "0 of 3", 3 MsgBox "Start of process" SysCmd acSysCmdInitMeter, "1 of 3", 3 SysCmd acSysCmdUpdateMeter, 1 MsgBox "Step 1 complete" SysCmd acSysCmdInitMeter, "2 of 3", 3 SysCmd acSysCmdUpdateMeter, 2 MsgBox "Step 2 complete" SysCmd acSysCmdInitMeter, "3 of 3", 3 SysCmd acSysCmdUpdateMeter, 3 MsgBox "All steps complete" SysCmd acSysCmdClearStatus
This is the sort of feature that's a perfect candidate for a class module. We take the time to figure out all the weirdness one time, build a class module that works the way we expect, save the class module in our code library, and then we benefit from the easier-to-call code every time we need to update the status bar in the future.
After we encapsulate the functionality inside a class module, we can produce clearer code:
Dim Status As New clsStatus Status.SetAll "0 of 3", 3, 0 MsgBox "Start of process" Status.SetAll "1 of 3", 3, 1 MsgBox "Step 1 complete" Status.SetAll "2 of 3", 3, 2 MsgBox "Step 2 complete" Status.SetAll "3 of 3", 3, 3 MsgBox "All steps complete" Status.Clear
clsStatus class module
Option Compare Database Option Explicit Private m_sText As String Private m_vMax As Variant Private m_vCurrent As Variant Private m_bMeterOn As Boolean Private Sub Class_Initialize() m_sText = " " m_bMeterOn = False End Sub Public Property Get Text() As String Text = m_sText End Property Public Property Let Text(ByVal sText As String) If Len(sText) = 0 Then m_sText = " " SysCmd acSysCmdClearStatus m_bMeterOn = False Else m_sText = sText If m_bMeterOn Then SysCmd acSysCmdInitMeter, sText, m_vMax SysCmd acSysCmdUpdateMeter, m_vCurrent Else SysCmd acSysCmdSetStatus, sText End If End If DoEvents End Property Public Sub Clear() m_sText = " " m_bMeterOn = False m_vMax = Null m_vCurrent = Null SysCmd acSysCmdClearStatus End Sub Public Property Get Max() As Variant Max = m_vMax End Property Public Property Let Max(ByVal vMax As Variant) m_vMax = vMax If IsNumeric(vMax) Then m_bMeterOn = True SysCmd acSysCmdInitMeter, m_sText, vMax Else m_bMeterOn = False End If End Property Public Property Get Current() As Variant Current = m_vCurrent End Property Public Property Let Current(ByVal vCurrent As Variant) m_vCurrent = vCurrent If IsNumeric(vCurrent) Then If m_bMeterOn Then SysCmd acSysCmdUpdateMeter, vCurrent End If End If DoEvents End Property Public Sub SetAll(sText As String, vMax As Variant, vCurrent As Variant) m_sText = IIf(Len(sText) > 0, sText, " ") m_vMax = vMax m_vCurrent = vCurrent m_bMeterOn = True SysCmd acSysCmdInitMeter, m_sText, vMax SysCmd acSysCmdUpdateMeter, vCurrent DoEvents End Sub
Software development is a journey
This class module is from the early days of my development career, hence the quasi-Systems Hungarian notation I used in naming my module variables. Joel Spolsky has since cured me of those foolish ways.
If I were to rewrite this class module today, I would name those four module-level variables
mMeterOn. In fact, I considered changing those variable names before I posted this code. After all, it would have only taken about 30 seconds to do a find and replace on those variables.
So, why didn't I do that?
There are a couple of reasons. First, I think it's important--especially for beginning programmers--to see that experienced programmers weren't just born that way. Nobody is perfect. If you're never horrified looking back at your own old code, then you've probably stopped progressing as a developer. So as long as my examples aren't negatively affecting your understanding of the concept, I'm going to try to force myself to just let it all hang out, warts and all.
Second, the code works just fine. Yes, the signal to noise ratio would be a little better without the two extra characters in each variable that don't convey any meaning. Yes, if I changed those variable names they would match my current naming convention better. But it wouldn't be that much better. If it ain't broke, don't fix it. Especially when all that logic is encapsulated inside a class module. After all, the whole point of a class module is that you're not supposed to care about how the thing works internally.