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
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
The Form_Error() Event
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.
Form_Error() procedure has two arguments,
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
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
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 DataErrstatement in the
Form_Errorevent. 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
- If you want to force Access to show the default error message, set the Response argument to
- 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 = acDataErrDisplayafter the ControlIsBlank calls.
- The default value for Response is
acDataErrDisplay. Setting it explicitly in the
Case Elsestatement 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
- Portions of this article's body generated with the help of ChatGPT