Filling in the Blanks

Mike Wolfe -

In yesterday's post, I wrote about "guard clauses."  I used a helper function named SaveFails to demonstrate the concept.  Today, I'm going to show you a different function that I commonly use in guard clauses: ControlIsBlank.

Like the SaveFails function, the ControlIsBlank function returns a boolean.  If the result is True, that generally indicates that we want to exit our routine.

I regularly use this on unbound forms where I want to require the user to enter a value for a control.  

Sample usage

For example, consider a report preview form that prompts the user for a Start and End Date.  Let's assume that we're querying an enormous table with years of history.  We want to avoid accidentally retrieving every single record, so we're going to force the user to enter a starting and ending date before they can preview the report.

Private Sub btnPreviewRpt_Click()
    If ControlIsBlank(Me.tbStartDate) Then Exit Sub
    If ControlIsBlank(Me.tbEndDate) Then Exit Sub
    
    Dim Criteria As String
    Criteria = "RptDate Between " & Dt(Me.tbStartDate) & _
                          " And " & Dt(Me.tbEndDate)
    DoCmd.OpenReport "MyReport", acViewPreview, , Criteria
End Sub
NOTE: See this article for the Dt() code

How it works

By default, the ControlIsBlank function performs the following tasks:

  1. Checks to see if the value of the passed control is "blank"
  2. Warns the user if the control is blank
  3. Sets focus to the control with the missing information
  4. Forces a list dropdown if the control is a ComboBox

Sometimes a control with no value will return Null and sometimes it will return an empty string.  I think that's a big part of the reason why I wrote this function in the first place.  Honestly, though, it's been so long since I've used anything but this function to check for an empty control, I don't remember what the different circumstances are that lead to the control's value being a Null versus an empty string.

The function will use the caption from an "attached label" (if there is one) to build the message it shows to the user.  Alternatively, you can pass it a description of the missing value in the CtlDesc argument (e.g., if there is no attached label) or the full text of the message you want to display to the user in the ErrMsg argument.

As long as the target control is both visible and enabled, the function will set the focus back to that control.  And, if that control is a ComboBox, then the function forces the ComboBox to drop down.  I have it force the dropdown for two reasons.  First, it makes it visually obvious to the user where the problem lies.  Second, it saves the user from having to hit the relatively small mouse target on the right side of the dropdown control.

The importance of library functions

As you can see, there's quite a bit of functionality packed into a tiny package.  And by calling the function from within a guard clause, there's zero readability cost.  We're not introducing any extra nesting, and the code needs no comments as it is self-documenting.

By investing the time up front to develop this function, I can have a much greater impact with my ongoing development.  Imagine writing this sort of functionality ad hoc everywhere I needed it.  Actually, why imagine it when I can show you what that would look like?  Obviously, we wouldn't need most of the functionality built into the generalized function, but it would still make our button click code much less readable:

Private Sub btnPreviewRpt_Click()
    If Nz(Me.tbStartDate, "") = "" Then 
        MsgBox "Please enter a Start Date"
        Exit Sub
    End If
    If Nz(Me.tbEndDate, "") = "" Then
        MsgBox "Please enter an End Date"
        Exit Sub
    End If
    
    Dim Criteria As String
    Criteria = "RptDate Between " & Dt(Me.tbStartDate) & _
                          " And " & Dt(Me.tbEndDate)
    DoCmd.OpenReport "MyReport", acViewPreview, , Criteria
End Sub

The above code is easy to write.  But it definitely took longer than the code from the top of this article.  What's more, it's less functional.  We certainly could add the lines to SetFocus to the appropriate controls, but would we?  And would we do it consistently?

Taking Making the easy way out

One of the keys to being a good developer is to make the easy way also be the right way.  Very often that means investing time up front to build a highly capable function that can also be called easily.  

Don't underestimate the value of sensible default values.  It's nice to have the flexibility to override the defaults in my ControlIsBlank function, but I would not use it nearly as much if I had to pass values for those other arguments on every call.

Along those same lines, try to assign short names to functions you will call a lot.  Good examples of this are the Dt() and Qt() functions I linked to earlier.  Functions that are called infrequently should have longer, more explicit names.  But for functions that get called repeatedly, excessively verbose names contribute more Noise than Signal.

This particular example violates that principal (which merely proves Ralph Waldo Emerson's point that "a foolish consistency is the hobgoblin of little minds").  I prefer the added function name length because it makes the guard clauses in which it appears read like English sentences.

More AutoHotkey goodness

Nevertheless, it's more typing than I prefer for such a common function.  To further enable my laziness, I created an AutoHotkey text expansion to save me some keystrokes.  Now when I type "cib" in the VBA IDE, AutoHotkey transforms it into "If ControlIsBlank(Me".

;VBA IDE
#IfWinActive ahk_class wndclass_desked_gsk
::cib::If ControlIsBlank(Me

Code: ControlIsBlank function

I'm closing in on a thousand words in this article, so I should probably just get to the code already:

'---------------------------------------------------------------------------------------
' Procedure : ControlIsBlank
' DateTime  : 9/16/2011 - 4/20/2015 12:59
' Author    : Mike@nolongerset.com
' Purpose   : Returns whether a control on a form is blank/null.  It also moves the focus
'             to that field and displays a message to the user (optional).
' Version 2 : 1/8/2008 10:08
'             If no Control Description is passed the function attempts to use the
'             caption of the attached label.  If that fails, it uses the name of the
'             control itself.
' Version 3 : 12/31/2008
'             If the control is a combo box, then the combo box drops down after
'             receiving the focus.
' Version 4 : 9/15/2014
'             Allow passing an optional full error message to display.
'---------------------------------------------------------------------------------------
'
Function ControlIsBlank(Ctl As Control, Optional ShowMsg As Boolean = True, _
                        Optional CtlDesc As String = "Attached_Label", _
                        Optional ErrMsg As String = vbNullString) As Boolean
    Dim Description As String

    ControlIsBlank = False

    If CtlDesc = "Attached_Label" Then
        On Error Resume Next
        Description = Ctl.Controls.Item(0).Caption
        If Err.Number <> 0 Then
            Description = Ctl.Name
            Err.Clear
        End If
        On Error GoTo 0
        'Use temporary placeholder (§) to handle escaped (eg, &&) and hotkey (eg, &Close) ampersands
        Description = Replace(Description, "&&", "§")
        Description = Replace(Description, "&", "")
        Description = Replace(Description, "§", "&")
    Else
        Description = CtlDesc
    End If

    If Nz(Ctl, "") = "" Then
        ControlIsBlank = True
        If ShowMsg Then
            Dim Msg As String
            If Len(ErrMsg) > 0 Then
                Msg = ErrMsg
            Else
                Msg = "Please enter a value for " & Description
            End If
            MsgBox Msg, vbExclamation, "Missing Data"
        End If
        If Ctl.Visible And Ctl.Enabled Then
            Ctl.SetFocus
            If Ctl.ControlType = acComboBox Then
                Ctl.Dropdown
            End If
        End If
    End If

End Function

P.S. As a reminder, the reason you never see the kind of traditional error handling code you're used to seeing in VBA examples is because I use a global error handler in conjunction with vbWatchdog.  Read all about that here: Error Handling Evolution.

Image by David Schwarzenberg from Pixabay