Fix a Microsoft Access Combo Box That Cannot Add New Locations
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.
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.
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.
In Access, select Create > Table Design. Add a Short Text field named Location, make it the primary key, and save the table as 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.
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.
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
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.

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.

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.




