ComboBox NotInList Event: Revisited

In a previous article, I wrote about how to dynamically update a combo box using its NotInList event.

In the comments section of that article, Ben Clothier raised an important caveat:

One major caveat with this technique, especially when using a dialog is that you cannot allow the user to edit the original value. As an example, if user typed in "PestB eG one", and a popup opens with the "PestB eG one" as the new vendor name. The user sees the typo, and then corrects to "Pest Be Gone" and saves the changes. This will then cause an error when returning to the combobox because it already has the "PestB eG one" and will be looking for it which no longer matches, then the user is presented with a cryptic error message about it being not in the list, only to find the "Pest Be Gone" listed after dismissing the Access built-in error message.

As usual, Ben is absolutely correct.  I thought my existing code already dealt with this situation, but it did not.  However, I was able to develop a fix for the error relatively quickly.  As an added bonus, it required me to use a relatively unknown form event that is worth knowing about even if you never use the NotInList event itself.

Reproducing the Error

The first step in any debugging process is to reproduce the error.

I trust Ben's knowledge, but I'm with Ronald Reagan on this one: "Trust, but verify."  Let's verify what happens when we follow Ben's steps:

As an example, if user typed in "PestB eG one",

and a popup opens with the "PestB eG one" as the new vendor name.

The user sees the typo,

and then corrects to "Pest Be Gone" and saves the changes.

This will then cause an error when returning to the combobox because it already has the "PestB eG one" and will be looking for it which no longer matches, then the user is presented with a cryptic error message about it being not in the list,

only to find the "Pest Be Gone" listed after dismissing the Access built-in error message.

Fixing the Error

In my response to Ben's comment, I gave a brief synopsis of how to work around this error:

In short, you look up the user-entered text using the newly created ID, explicitly assign it to the combo box, and set a flag to ignore the cryptic Access error message.  Then handle the Form_Error event, ignoring the NotInList error if the flag is set (and then clear the flag).

Let's step through this synopsis as we did for Ben's comment above.

In short, you look up the user-entered text using the newly created ID, explicitly assign it to the combo box,

Me.VendorID.Text = DLookup("FullName", "Vendors", "VendorID=" & NewVendorID)

and set a flag to ignore the cryptic Access error message.  

mIgnoreNotInListError = True

Then handle the Form_Error event, ignoring the NotInList error if the flag is set (and then clear the flag).

Dim mIgnoreNotInListError As Boolean  ' <-- In form module header section

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2237 And mIgnoreNotInListError Then
        'Hide the error message
        Response = acDataErrContinue
        
        'Clear the flag
        mIgnoreNotInListError = False
    End If
End Sub

After applying these changes, I stepped through Ben's scenario a second time.  The steps are all identical through the point where the user fixes the typo in the Vendor Detail form:

After closing that form, the fixed vendor name is shown and pre-selected in the combo box:

About the Form Error Event

Many readers may not recognize the Form Error event.

It gets raised when there is an error in the form's built-in functionality (i.e., not your custom VBA code).

There are a few ways to recognize these types of errors:

• They appear in a formatted message box where the top line is bold, followed by a blank line, and then a non-bold additional message.
• The error is not related to any custom code you wrote.
• Pressing Ctrl+Break does not enter break mode in the VBA editor; it just dismisses the message.

For a long time, I thought these sorts of errors simply couldn't be trapped.  It turns out they can sort of be "pre-trapped" within the form (or report) Error event.  

For more information about the Form Error event, check out my article on the subject, The Form Error Event: How to Handle "Untrappable" Access Errors.