How to Fix Access Combo Box Showing IDs Instead of Text
Question details
The user needs to configure a database combo box to display descriptive text instead of the numeric primary keys, and easily add new lookup values.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a data entry form and configuring a lookup field to reference another table's values.
- Observed behavior
- The combo box incorrectly displays numeric ID values from the related table instead of the descriptive names, confusing users and making data entry difficult.
Before modifying your database schema or form properties, ensure you have backed up your Microsoft Access database file to prevent accidental data loss during design changes.
Configure Combo Box Column Widths on a Form
Hide the numeric primary key by adjusting the column width properties of the combo box, forcing it to display the descriptive text instead.
It is a best practice to avoid table-level lookups as they can obscure the actual stored data. Instead, implement your lookups directly on data entry forms using combo boxes.
Right-click your form in the navigation pane and select Design View. Click on the combo box you want to modify.
Press F4 on your keyboard to open the Property Sheet pane on the right side of the screen.
Navigate to the Data tab. Ensure the Bound Column property is set to 1 (this represents the numeric ID from your Row Source query).
Switch to the Format tab. Ensure the Column Count matches the number of fields in your query (usually 2). Change the Column Widths property to hide the first column by setting its width to zero, such as 0"; 1.5" (or 0cm; 4cm depending on regional settings).
Use the NotInList Event to Add New Values
Configure the combo box to safely allow users to add new text entries without causing primary key relational errors.
Need a Lightweight Office Suite? Try WPS Office
While Microsoft Access handles complex relational databases, many everyday list-tracking and data management tasks can be easily managed using WPS Spreadsheet. WPS Office offers a free, highly compatible alternative for your document, spreadsheet, and presentation needs with a familiar user interface.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded file and follow the on-screen instructions to install the suite on your computer.
- 3. Manage Data in Spreadsheets: Open WPS Spreadsheet, use Data Validation to create simple drop-down lists, and easily track your data without complex database configurations.
Frequently Asked Questions
Why does my Access combo box display numbers instead of names?
This happens when the combo box's Bound Column is set to the primary key (the ID number) and the Column Widths property isn't configured to hide it. To fix this, set the Column Width of the first column to 0 in the Format properties tab.
Should I use lookup fields directly in Microsoft Access tables?
No, database professionals generally recommend avoiding table-level lookup fields. They can obscure the actual stored data (numeric IDs) and cause confusion when writing queries. Instead, implement lookups using combo boxes directly on your data entry forms.
How do I let users add new items to a combo box list?
You can allow additions by setting the Limit to List property to Yes and utilizing the On Not In List event. This event can trigger a macro or VBA code to open a separate entry form, allowing the user to correctly add the new record to the underlying table.
