Showing User-Friendly Error Messages for Missing Data on Bound Forms in MS Access

One of the functions I use most in my applications is ControlIsBlank().

I use it almost exclusively as a guard clause on unbound forms when I have one or more text boxes or combo boxes that I want a user to fill in before continuing.  For example:

Private Sub btnPreview_Click()
    If ControlIsBlank(Me.tbStartDate) Then Exit Sub
    If ControlIsBlank(Me.tbEndDate) Then Exit Sub
    
    'Build report criteria and preview a report
End Sub

Using ControlIsBlank() with Bound Forms

A reader asked recently whether this function could be used with bound forms to replace the not-very-user-friendly default message that appears:

Now, the above message isn't too bad because the field name is straightforward.  Not all field names are.  One of the nice features of the ControlIsBlank() function is that, by default, it uses the text from the attached label to generate the message box.  Additionally, it sets the focus to the field with the missing text so the user doesn't have to wonder where the problem is.

I've never used my ControlIsBlank function on a bound form before, but I was pretty sure it could be done.

The Simple Approach

The simplest approach would be to include the calls to ControlIsBlank() in the form's BeforeUpdate event as guard clauses:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = True
    If ControlIsBlank(Me.tbFirstName) Then Exit Sub
    If ControlIsBlank(Me.tbLastName) Then Exit Sub
    Cancel = False  'Validity checks passed
    
    'Additional code if desired
End Sub

I set Cancel = True at the beginning of the subroutine to avoid having to set it after each ControlIsBlank call.  That allows me to keep my guard clauses as one-liners, which makes everything neat and tidy.

The Unnecessarily Complicated Approach

Honestly, I wasn't sure what the form event order was and whether the above approach would work.  I was pleasantly surprised to see that it did.  But not before I went down a different rabbit hole that you might not even know about.  So I'll introduce that concept here.  It's the Form_Error() event.

The Form_Error() Event

The Form_Error() event is a built-in event that occurs when an error is encountered while entering or editing data in a form. The event is triggered when a user attempts to save a record that contains errors, such as when a required field is left blank or when a value is entered that is not valid for a field's data type.

The event can be used to perform custom error handling, such as displaying a custom error message or allowing the user to correct the error and continue with data entry.  

The Form_Error() procedure has two arguments, DataErr and Response. The DataErr argument is an integer that contains the error code for the error that occurred, and the Response argument is an integer that controls the behavior of the event. The constants acDataErrDisplay, acDataErrAdded, and acDataErrContinue can be used to control the behavior of the event.

You can use the DataErr argument to determine the specific type of error that occurred and perform custom error handling based on the error code.

It's important to note that the Form_Error() event will only be triggered when an error occurs during data entry, not when an error occurs in other parts of the application.

Form_Error() Event Example

Before I had tested the Form_BeforeUpdate() approach, I had already put together the following sample code using Form_Error().  

Using BeforeUpdate makes more sense in this situation, but that won't always be the case.  Also, having two different samples that accomplish the same thing clarifies some of the unique requirements of handling the Form_Error() event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3314  'You must enter a value in the '|' field
        Response = acDataErrContinue
        If ControlIsBlank(Me.tbFirstName) Then Exit Sub
        If ControlIsBlank(Me.tbLastName) Then Exit Sub
        Response = acDataErrDisplay
    Case Else
        Debug.Print DataErr, AccessError(DataErr)
        Response = acDataErrDisplay
    End Select
End Sub

Some important items to note:

• You'll almost always want a Select Case DataErr statement in the Form_Error event.  This lets you provide special handling for expected errors without unintentionally swallowing unexpected errors.
• To suppress the default error message, you MUST set the Response argument to acDataErrContinue.
• If you want to force Access to show the default error message, set the Response argument to acDataErrDisplay.
• I only have guard clauses for the FirstName and LastName text boxes.  Since there may be other required fields (now or in the future), I re-enable the default error message via Response = acDataErrDisplay after the ControlIsBlank calls.
• The default value for Response is acDataErrDisplay.  Setting it explicitly in the Case Else statement is not strictly necessary, but it makes it clear that I intend for the message to be displayed.
• You can use the AccessError() function to show the default message associated with the various DataErr codes.

Cover image created with Microsoft Designer

Acknowledgements

• Portions of this article's body generated with the help of ChatGPT