Others

Handle the Microsoft Access Combo Box Not In List Event

Steven ParkerSteven Parker Nov 22, 2026 868 views

Question details

Handle country names entered into an Access combo box without treating entries with extra spaces as new countries.

Handle the Microsoft Access Combo Box Not In List Event
Product
Microsoft Access
Device & OS
not provided
Scenario
A form uses a combo box with Limit To List enabled to select or add a country.
Observed behavior
Extra spaces can trigger the Not In List event for an existing country, while a value list makes country data difficult to validate and maintain.
Before you start

Open the form in Design View and identify the combo box and the table that will store country names. The example uses a combo box named cboCountry and a table named tblCountry with a text field named Country; adjust the names to match your database.

Solution 1Recommended

Check a country table in the Not In List event

Use a table-backed combo box and trim the entered name before checking for duplicates or adding a confirmed country.

The example compares whole country names rather than using a wildcard search, which could mistake a partial match for a duplicate. It also trims leading and trailing spaces from both the entered value and stored names.

1
Create the country table

In Access, create or open tblCountry and add a Short Text field named Country. Enter your existing country names, removing unwanted leading or trailing spaces and duplicates.

2
Set the combo box source

Open the form in Design View, select cboCountry, and open the Property Sheet. On the Data tab, set Row Source Type to Table/Query, Row Source to SELECT Country FROM tblCountry ORDER BY Country;, and Limit To List to Yes. For this example, use one displayed and bound column containing Country.

3
Open the event procedure

In the combo box Property Sheet, open the Event tab. Set On Not In List to [Event Procedure], then click the ellipsis button to open the VBA editor.

4
Add the validation code

Replace the generated cboCountry_NotInList procedure with the following code: Private Sub cboCountry_NotInList(NewData As String, Response As Integer) Dim countryName As String Dim safeName As String On Error GoTo AddError Response = acDataErrContinue countryName = Trim(NewData) If Len(countryName) = 0 Then MsgBox "Enter a country name or select one from the list." Exit Sub End If safeName = Replace(countryName, "'", "''") If DCount("*", "tblCountry", "Trim([Country]) = '" & safeName & "'") > 0 Then MsgBox "This country already exists. Please select it from the list." Exit Sub End If If MsgBox("Add " & countryName & " to the country list?", vbYesNo + vbQuestion) = vbYes Then CurrentDb.Execute "INSERT INTO tblCountry ([Country]) VALUES ('" & safeName & "')", dbFailOnError Response = acDataErrAdded End If Exit Sub AddError: MsgBox "The country could not be added: " & Err.Description End Sub

5
Test the form

Save the form, open it in Form View, and type an existing country with extra spaces. Confirm that the message asks you to select the existing entry, then test adding a new country and declining an addition.

Check a country table in the Not In List event
Protect against duplicates: After cleaning existing data, consider setting the Country field's Indexed property to Yes (No Duplicates) in table Design View. The event code validates this combo box, while a unique index also protects other entry paths.
Free Microsoft Office alternative

Manage country lists in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility for easier migration. Use WPS Spreadsheets to review and maintain country lists, but use Microsoft Access to run and troubleshoot the combo box event.

Review and organize country lists in a spreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for everyday workGet a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does an existing country trigger Not In List?

With Limit To List set to Yes, Access fires the event when the typed text does not match a list entry. Leading or trailing spaces can prevent a match, so trim NewData and check the country table in the event procedure.

What does acDataErrContinue do?

Setting Response to acDataErrContinue suppresses the default Access error message. In this example, it lets your message tell the user to select an existing country or enter a valid name.

When should I use acDataErrAdded?

Set Response to acDataErrAdded only after the new country has been successfully inserted into the combo box's source table. Access can then requery the list and accept the added value.

Can I keep using a value list for countries?

A value list can work for a short, static set of entries, but a table is easier to query and maintain. Set the combo box's Row Source Type to Table/Query and select the country field from the table.