Using the NotInList Event in Microsoft Access to Dynamically Update Combo Boxes

Building an intuitive user interface can be deceptively hard.

One way to build an intuitive UI is to anticipate what your user will need to do next and provide it for her without her even having to go looking for it.

The Access combo box control's NotInList event is a great way to do that.

Just-In-Time Features

The NotInList event–along with my weComboLookup custom class–allows you to provide your users with several "just-in-time" features.

Imagine an Accounts Payable program where you track and pay invoices from vendors.  The first step in creating a new invoice is to select a vendor.  To do this, the UI provides a combo box bound to the Vendor table:

The combo box includes all the vendors that have ever been paid through the system, including inactive vendors (which appear at the bottom of the sort order).

The combo box also implements progressive filtering.  This means that even if the user-entered text does not match the beginning of any vendor names, the combo box's row source will dynamically update to show vendors where the text appears anywhere within the vendor name:

The progressive filtering feature plays a vital role.  It helps prevent our users from inadvertently adding duplicate vendors.  Without this feature, users could easily end up adding a new vendor because they forgot the Hartford insurance company is actually The Hartford insurance company:

The original point of this article was to discuss the NotInList event, so let's get back on track.

NotInList Example

When designing an application, it's often helpful to step back and ask, "How will this be used in real life?"

In all likelihood, this application will involve someone in accounting sitting at his desk with a stack of vendor invoices.  Most of these invoices will be from vendors already in the system.  Mixed in the pile of existing vendors, though, will be the occasional invoice from a new vendor, let's call them, "Pest Be Gone".

The accounting clerk is flying through entering these invoices when he starts typing "Pest" into the Vendor dropdown and realizes "Pest Be Gone" is not an option.

At this point, he could close the Add New Invoice form, go back to the Main Menu, open the Vendors lookup form, click the button to add a new vendor, add all the new vendor information, close the vendor detail form, close the Vendors lookup form, go back to the Main Menu, click on the Invoices button, click Add New Invoice, choose "Pest Be Gone" from the dropdown, and continue on his merry way.

That feels like a lot of steps.

Let's short circuit that process with the NotInList event.

The accounting clerk is flying through entering these invoices when he starts typing "Pest" into the Vendor dropdown and realizes "Pest Be Gone" is not an option.

At this point, he finishes typing "Pest Be Gone" into the combo box and presses Enter, clicks [Yes] when the program asks if he wants to add a new vendor, the vendor detail form opens (with "Pest Be Gone" already prefilled in the vendor name field), he adds the remaining new vendor information, closes the vendor detail form, and continues on his merry way.

That is a simple user interface.

NotInList Sample Code

Here's my sample code with a documentation assist from ChatGPT (the original code has no comments).  Obviously, you won't be able to just copy and paste this into your own project, as it references controls on custom forms in my application.  However, it should be enough to get you pointed in the right direction.

Private Sub VendorID_NotInList(NewData As String, Response As Integer)
    ' This subroutine is triggered when a value entered in a form's VendorID field is not found in the vendor list.
    ' It provides the user with an option to add the new vendor to the list.

    Dim Result As VbMsgBoxResult, NewVendorID As Long, MaxVendorID As Long

    On Error GoTo Err_VendorID_NotInList

    ' Set the response argument to display an error message when the entered data is not found in the vendor list.
    Response = acDataErrDisplay

    ' Display a message box asking the user if they want to add the new vendor to the list.
    Result = MsgBox("'" & NewData & "' is not currently a vendor. Would you like to add '" & _
                      NewData & "' to the vendor list?", vbYesNo + vbExclamation, "Vendor Not In List")

    If Result = vbYes Then
        ' If the user chooses to add the vendor, open the VendorDetail form in "Add" mode.
        ShowForm "VendorDetail", , "Add"

        ' Find the maximum VendorID value currently in the Vendors table.
        MaxVendorID = DMax("VendorID", "Vendors")

        ' Set the Full Name and Last Name fields in the VendorDetail form to the entered NewData.
        Form_VendorDetail.FullName = NewData
        Form_VendorDetail.LastName = NewData

        ' Wait until the VendorDetail form is closed by the user.
        '    https://nolongerset.com/waittilobjclosed/
        WaitTilObjClosed acForm, "VendorDetail"

        ' Find the maximum VendorID value again after the form is closed.
        NewVendorID = DMax("VendorID", "Vendors")

        ' If a new VendorID was generated, update the current form's VendorID field.
        If NewVendorID > MaxVendorID Then
            Me.VendorID = NewVendorID
            Me.VendorID.Requery
            ' Set the response to indicate that a new record has been added.
            Response = acDataErrAdded
        End If
    End If

Exit_VendorID_NotInList:
    Exit Sub

Err_VendorID_NotInList:
    Select Case Err.Number
    Case Else
        ' Log any errors that occur during the process.
        LogError Err.Number, Err.Description, "VendorID_NotInList", "Form_AddInvoice"
    End Select
    ' Resume processing after handling the error.
    Resume Exit_VendorID_NotInList
End Sub

The code for my WaitTilObjClosed() function is available here:

How to Pause Code Execution Until a Form or Report is Closed (Without Using acDialog)

Do you need to pause your code until the user closes a form or report but are running into problems using acDialog? This simple function is your answer.

No Longer SetMike Wolfe

Additional references

A Wonderful, Magical Class Module

Imagine a single class module you can use for progressive combo box filtering, lazy loading combo boxes, AND multi-column filtering!

No Longer SetMike Wolfe

Simple vs. Easy

It’s not easy to build simple software. These 4 simple tips will make the process easier.

No Longer SetMike Wolfe