The On Not in List event in Microsoft Access is triggered when a user types a value into a combo box that doesn’t currently exist in its list of available options if the Limit To List property (on the Data tab of the Property Sheet) is set to Yes. This event allows you to manage how the application responds, most commonly by offering the user a way to add the new value directly to the combo box’s data source which is most often a table.
For instance, imagine you have a combo box that lists available product categories from a Categories table. When a user enters a new category say, “Eco-Friendly” that isn’t already in the list, the Not In List event can prompt the user to confirm whether they’d like to add it. If they agree, your VBA code can insert the new category into the Categories table and then requery the combo box so that the new option appears immediately.
This approach helps maintain consistent data while giving users flexibility to expand predefined lists dynamically, improving workflow and usability.
The Typical Approach
Typically, we hard code each Not In List event procedure directly in the form module using a similar pattern:
Private Sub YourCboName_NotInList(NewData As String, Response As Integer)
'Requires that a reference to the Microsoft DAO 3.6 Object Library
On Error GoTo Error_Handler
Dim oRS As DAO.Recordset
If MsgBox(NewData & "... not in list, add it?", vbOKCancel, "MessageBoxTitle") = vbOK Then
Set oRS = CurrentDb.OpenRecordset("TableName") 'Table to add the new value to
With oRS
.AddNew
.Fields("TableColumnName") = NewData 'Name of the table field to add the new value to
.Update
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Error_Handler_Exit:
On Error Resume Next
oRS.Close
Set oRS = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: YourCboName_NotInList" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Improved Approach – Level 1
There’s a better way!
Instead of hard-coding each Not In List event individually, create a reusable function you can call from all your combo box events. Over the years, my function has evolved into a robust, parameterized version that handles the table name, field name, and combo box reference dynamically.
'---------------------------------------------------------------------------------------
' Procedure : Form_NotInList
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Function to insert new entries into a table as part of a Not In List event
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: DAO/Microsoft Office XX.0 Access database engine Object Library
' Dependencies: RichBox
' https://www.devhut.net/great-access-tools-enhanced-message-box/
' ***** Can easily be converted back to plain MsgBox *****
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sNewData : Not In List NewData
' sTable : Table to insert the new value into
' sPkFieldName : Primary Key field of the 'sTable'
' sFieldName : Field to insert the new value into
' sPlural : Textual plural of the category in which the new data is being inserts
' sLanguage : (Optional) What language to display the confirmation dialog in
' sNewForm : (Optional) Name of the form to open after the insertion occurs
'
' Usage:
' ~~~~~~
' Response = Form_NotInList(NewData, "lst_Category", "CategoryId", "Category", "Categories", "En")
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2010-06-12 Initial Release
' 2 2018-02-19 Updated function Header
'---------------------------------------------------------------------------------------
Public Function Form_NotInList(ByVal sNewData As String, _
ByVal sTable As String, _
ByVal sPkFieldName As String, _
ByVal sFieldName As String, _
ByVal sPlural As String, _
Optional ByVal sLanguage As String = "En", _
Optional ByVal sNewForm As String) As Integer
On Error GoTo Error_Handler
Dim oRS As DAO.Recordset
Dim sPkField As String
Dim sMessage As String
Dim sTitle As String
Dim lNewId As Long
' Define the message box text or you could use a Translation function
Select Case sLanguage
Case "Fr"
sTitle = "Ajout de nouvelles données"
sMessage = "'<b>" & sNewData & "</b>' ne fait pas parti de la liste de choix existante dans la liste des " & sPlural & ".<br /><br />" & _
"Voulez-vous le r'ajouter?" & "<br /><br />" & _
"(<em>Veuillez vérifier la synthax avant de continuer</em>)."
Case Else
sTitle = "Add New Data"
sMessage = "'<b>" & sNewData & "</b>' is not in the current list of " & sPlural & ".<br /><br />" & _
"Would you like to add it?<br /><br />" & _
"(<em>Please check the entry before proceeding</em>)."
End Select
' Display message box asking if user wants to add the new item
If Dialog.RichBox(sMessage, vbYesNo + vbQuestion + vbDefaultButton2, sTitle, , , 0) = vbYes Then
' If Yes, then append the value to the table
Set oRS = CurrentDb.OpenRecordset(sTable, , dbAppendOnly)
oRS.AddNew
oRS(sFieldName) = sNewData ' Add new data from combo box
oRS.Update
lNewId = oRS(sPkFieldName) ' Determine the newly added record's PK value
DoEvents
' Open the requested form & filter it to the newly added record we just created, if applicable
If sNewForm <> "" Then DoCmd.OpenForm sNewForm, , , "[" & sPkFieldName & "]=" & lNewId, , acDialog
' Return the NotInList response
Form_NotInList = acDataErrAdded ' Data added
Else
' Return the NotInList response
Form_NotInList = acDataErrContinue ' Data NOT added
End If
Error_Handler_Exit:
On Error Resume Next
oRS.Close
Set oRS = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: Form_NotInList" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
The beauty here is now our Not In List event becomes:
Private Sub YourCboName_NotInList(NewData As String, Response As Integer)
Response = Form_NotInList(NewData, "lst_Category", "CategoryId", "Category", "Categories", "En")
End Sub
So, a single line!
Simplifying the Function
This function was created while developing one of the multilingual databases I’ve created and that is why you see a sLanguage input argument and a Select Case statement within the function. If you truly want, you could always remove those to simplify the function ever so slightly as I know most people do not support such multilingual solutions. You can also switch it to use the conventional MsgBox() instead of the RichBox dialog to remove any dependencies. Below is a quick example of this:
Public Function Form_NotInList(ByVal sNewData As String, _
ByVal sTable As String, _
ByVal sPkFieldName As String, _
ByVal sFieldName As String, _
ByVal sPlural As String, _
Optional ByVal sNewForm As String) As Integer
On Error GoTo Error_Handler
Dim oRs As DAO.Recordset
Dim sPkField As String
Dim sMessage As String
Dim lNewId As Long
' Define the message box text
sMessage = "'" & sNewData & "' is not in the current list of " & sPlural & "." & Chr(13) & Chr(13) & _
"Would you like to add it?" & Chr(13) & Chr(13) & _
"(Please check the entry before proceeding)."
' Display message box asking if user wants to add the new item
If MsgBox(sMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
' If Yes, then append the value to the table
Set oRs = CurrentDb.OpenRecordset(sTable, , dbAppendOnly)
oRs.AddNew
oRs(sFieldName) = sNewData ' Add new data from combo box
oRs.Update
lNewId = oRs(sPkFieldName) ' Determine the newly added record's PK value
DoEvents
' Open the requested form & filter it to the newly added record we just created, if applicable
If sNewForm <> "" Then DoCmd.OpenForm sNewForm, , , "[" & sPkFieldName & "]=" & lNewId, , acDialog
' Return the NotInList response
Form_NotInList = acDataErrAdded ' Data added
Else
' Return the NotInList response
Form_NotInList = acDataErrContinue ' Data NOT added
End If
Error_Handler_Exit:
On Error Resume Next
oRs.Close
Set oRs = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: Form_NotInList" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
which then is used by doing:
Private Sub YourCboName_NotInList(NewData As String, Response As Integer)
Response = Form_NotInList(NewData, "lst_Category", "CategoryId", "Category", "Categories")
End Sub
but I urge you to use the RichBox as it truly enhances the user experience and make you final solution much more professional!
Improved Approach – Level 2
The basic reusable function works great and covers 85-95% of typical scenarios. However, some cases require setting multiple fields when adding a new entry like when users input a new region (province/state) or city.
For example, adding a new region shouldn’t just save the name; it typically needs the associated country too, ensuring proper data integrity.
This limitation led me to refine the approach further, eventually creating an enhanced version that supports multiple field assignments dynamically.
'---------------------------------------------------------------------------------------
' Procedure : Form_NotInList_Adv
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Function to insert new entries into a table as part of a Not In List event
' but this one can handle multiple fields
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: DAO/Microsoft Office XX.0 Access database engine Object Library
' Dependencies: RichBox
' https://www.devhut.net/great-access-tools-enhanced-message-box/
' ***** Can easily be converted back to plain MsgBox *****
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sNewData : Not In List NewData
' sTable : Table to insert the new value into
' sPkFieldName : Primary Key field of the 'sTable'
' vFields : Array pair(s) of the field/value to insert
' sPlural : Textual plural of the category in which the new data is being inserts
' sLanguage : (Optional) What language to display the confirmation dialog in
' sNewForm : (Optional) Name of the form to open after the insertion occurs
'
' Usage:
' ~~~~~~
' See example
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2010-06-12 Initial Release
' 2 2018-02-19 Updated function Header
' 3 2019-05-05 Changed to vFields
'---------------------------------------------------------------------------------------
Public Function Form_NotInList_Adv(ByVal sNewData As String, _
ByVal sTable As String, _
ByVal sPkFieldName As String, _
ByVal vFields As Variant, _
ByVal sPlural As String, _
Optional ByVal sLanguage As String = "En", _
Optional ByVal sNewForm As String) As Integer
On Error GoTo Error_Handler
Dim oRS As DAO.Recordset
Dim sPkField As String
Dim sMessage As String
Dim sTitle As String
Dim lNewId As Long
Dim i As Long
' Define the message box text or you could use a Translation function
Select Case sLanguage
Case "Fr"
sTitle = "Ajout de nouvelles données"
sMessage = "'<b>" & sNewData & "</b>' ne fait pas parti de la liste de choix existante dans la liste des " & sPlural & ".<br /><br />" & _
"Voulez-vous le r'ajouter?" & "<br /><br />" & _
"(<em>Veuillez vérifier la synthax avant de continuer</em>)."
Case Else
sTitle = "Add New Data"
sMessage = "'<b>" & sNewData & "</b>' is not in the current list of " & sPlural & ".<br /><br />" & _
"Would you like to add it?<br /><br />" & _
"(<em>Please check the entry before proceeding</em>)."
End Select
' Display message box asking if user wants to add the new item
If Dialog.RichBox(sMessage, vbYesNo + vbQuestion + vbDefaultButton2, sTitle, , , 0) = vbYes Then
' If Yes, then append the value to the table
Set oRS = CurrentDb.OpenRecordset(sTable, , dbAppendOnly)
oRS.AddNew
For i = LBound(vFields, 1) To UBound(vFields, 1)
oRS(vFields(i, 0)) = vFields(i, 1)
Next i
oRS.Update
lNewId = oRS(sPkFieldName) ' Determine the newly added record's PK value
DoEvents
' Open the requested form & filter it to the newly added record we just created, if applicable
If sNewForm <> "" Then DoCmd.OpenForm sNewForm, , , "[" & sPkFieldName & "]=" & lNewId, , acDialog
' Return the NotInList response
Form_NotInList_Adv = acDataErrAdded ' Data added
Else
' Return the NotInList response
Form_NotInList_Adv = acDataErrContinue ' Data NOT added
End If
Error_Handler_Exit:
On Error Resume Next
oRS.Close
Set oRS = Nothing
Exit Function
Error_Handler:
LogError Err.Number, Err.Description, "Form_NotInList_Adv", , True
Resume Error_Handler_Exit
End Function
and to use this one we simply do:
Private Sub RegionId_NotInList(NewData As String, Response As Integer)
Dim vFields(1 To 2, 0 To 1) As Variant
vFields(1, 0) = "Region": vFields(1, 1) = NewData
vFields(2, 0) = "CountryId": vFields(2, 1) = Me.CountryId
Response = Form_NotInList_Adv(NewData, "lst_Region", "RegionId", vFields, "Regions", "En")
If Response = acDataErrAdded Then
Me.CityId = Null
Me.CityId.Requery
End If
End Sub
As you can see, it is just a question of initially defining the array of fields/values and passing that to the function, just like in the previous example.
It is these ‘features’ that start to make your database feel like a well oiled and polish solution rather than a simple Access database. These are the types of things that truly show a consideration for the end-user experience and are essential for ensuring properly normalized data.
Page History
| Date | Summary of Changes |
|---|---|
| 2010-06-12 | Initial Release |
| 2026-02-17 | Completely revamped the entire article and added ‘Improved Approach’ sections |
| 2026-02-19 | Add YouTube Video |