Using the NotInList Event in Microsoft Access to Dynamically Update Combo Boxes
The Microsoft Access combo box's NotInList event lets you provide "just-in-time" features for building a simple and intuitive user experience.
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:
Mike Wolfe
Additional references
Mike Wolfe
Mike Wolfe
