Others

Fix a Microsoft Access Combo Box That Cannot Add New Locations

Algirdas JasaitisAlgirdas Jasaitis Nov 14, 2026 868 views

Question details

The user needs an Access Location combo box to save newly entered locations for future selection.

Product
Microsoft Access
Device & OS
not provided
Scenario
An asset-tracking form uses a combo box to select or enter a location.
Observed behavior
New locations are no longer saved in the combo box list. Compact and Repair and reinstalling Office did not resolve the issue.
Before you start

Open the asset form in Design View and check the Location combo box's Row Source Type, Row Source, Control Source, and Not In List event in the Property Sheet. Confirm that you can edit the database design and run VBA code.

Solution 1Recommended

Store locations in a table and add new entries with VBA

Use a table-backed combo box when locations need to be added and reused over time.

The example uses a text-based asset field named Location, an asset table named Assets, and a new table named Locations. Replace these names with your actual table and field names. If your asset table stores numeric location IDs instead of location text, adapt the table, bound column, and insert logic to that design.

1
Create the locations table

In Access, select Create > Table Design. Add a Short Text field named Location, make it the primary key, and save the table as Locations.

2
Populate existing locations

If Locations is empty, select Create > Query Design > SQL View and run: INSERT INTO Locations (Location) SELECT DISTINCT Assets.Location FROM Assets WHERE Assets.Location Is Not Null AND Assets.Location <> ''; Replace Assets and Location with your actual names. Review the results in the Locations table.

3
Set the combo box source

Open the form in Design View, select the Location combo box, and open its Property Sheet. On the Data tab, set Row Source Type to Table/Query and Row Source to: SELECT Location FROM Locations ORDER BY Location; Set Limit To List to Yes. For this one-column example, set Column Count and Bound Column to 1, and confirm Control Source points to the asset's text Location field.

4
Add the event procedure

On the combo box's Event tab, set On Not In List to [Event Procedure] and click the ellipsis button. In the generated procedure, add: On Error GoTo AddFailed CurrentDb.Execute "INSERT INTO Locations (Location) VALUES ('" & Replace(NewData, "'", "''") & "')", dbFailOnError Response = acDataErrAdded Exit Sub AddFailed: MsgBox "Could not add the location: " & Err.Description Response = acDataErrContinue

5
Test a new location

Save the form, open it in Form View, type a new location into the combo box, and move to another control. Reopen the list to confirm the location appears, and check the Locations table if it does not.

Store locations in a table and add new entries with VBA
Enable trusted code: The Not In List procedure runs only when Access allows VBA. If a security warning appears, enable content only if you trust the database. The database user also needs permission to insert records into Locations.
Free Microsoft Office alternative

Manage Simple Location Lists in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility. It can help you move spreadsheet-based location lists into a familiar workspace, but it does not repair Access databases or run Access forms and VBA.

Maintain a simple asset and location list in WPS Spreadsheets.Open and edit Microsoft Excel XLSX files.Use a familiar spreadsheet interface for an easier transition from Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why use a table instead of a Value List for locations?

A table is easier to maintain as the list grows and lets the Not In List event insert new locations as records. A Value List is better suited to a short, static set of choices.

Why doesn't the Not In List event run when I type a new location?

In the combo box's Property Sheet, set Limit To List to Yes and confirm that On Not In List is set to [Event Procedure]. Also check that VBA content is enabled for the trusted database.

How do I prevent duplicate locations?

In Locations table Design View, make the Location field a primary key or give it a unique index. The insert will then reject a duplicate, and the event's error handler can report the problem.

Will WPS Office fix the Access combo box?

No. The combo box and its VBA event must be repaired in Microsoft Access. WPS Spreadsheets can manage a separate location list in an Excel-compatible workbook.