Don't Settle for the "Status" Quo

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 mText, mMax, mCurrent, and 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.

Image by congerdesign from Pixabay