How to Save Multiple Combo Box Values in an Access Form
Question details
The user wants to save multiple values populated by a single combo box into a database table, but only the primary key is saving while the displayed descriptions are not.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Using a combo box on a form to auto-fill multiple fields and attempting to save all displayed values to a new record.
- Observed behavior
- The combo box displays the associated values on the form using expressions, but these expression-based values are unbound and therefore not saved to the underlying table.
Review your database structure to ensure you aren't violating normalization rules by unnecessarily duplicating data across tables.
Use Queries to Display Related Data (Best Practice)
In a well-designed relational database, data should only be stored in one place. Use this method to view descriptions without saving redundant data.
Controls that use expressions as their control source are unbound, meaning they will not save data to your table. Storing a description in multiple tables violates database normalization principles.
Instead of saving the derived data again, you should only save the primary key (e.g., Part Number) in your main table and use a query to retrieve the related descriptions whenever you need to view or print the data.
Ensure your combo box is bound to the foreign key field in your table, so only the unique identifier is saved to the record.
Go to the Create tab on the Ribbon and click Query Design. Add both your main table and the lookup table that contains the descriptions.
Ensure there is a relationship line joining the key fields of both tables. Drag the foreign key from the main table and the description field from the lookup table into the query grid.
Save the query and use it as the Record Source for any future forms or reports where you need the full description displayed alongside the key.
Save Values using VBA (For Historical Data Preservation)
Use this method only if the description might change in the future and you need to preserve the exact text at the time the record was originally created.
Need a simpler way to manage data? Try WPS Spreadsheet
While Microsoft Access is powerful for relational databases, managing data relationships can be complex. If you need a lightweight, intuitive way to organize, analyze, and track data without writing code or building complex forms, WPS Office provides an excellent, free alternative. It is fully compatible with Excel formats and offers powerful data validation and lookup tools to auto-fill related data easily.
Frequently Asked Questions
Why does my Access text box show #Name?
This usually happens when a text box is bound to a field that doesn't exist in the form's Record Source, or if the expression typed into the Control Source contains a syntax error or references a deleted control.
How do I reference a specific column in an Access combo box?
You can reference a combo box column using the Column property. In Access, columns are zero-indexed, meaning `.Column(0)` refers to the first column (usually the hidden Primary Key), and `.Column(1)` refers to the second column.
Should I save calculated fields in an Access table?
Generally, no. Calculated fields or derived data should be calculated on the fly using queries, forms, or reports. This maintains database normalization, saves storage space, and prevents data inconsistencies.
Can I auto-fill a form field without saving it to the table?
Yes. You can leave the text box unbound and set its Control Source to an expression like `=ComboName.Column(1)`. This will display the associated data for user reference without improperly duplicating it in your underlying table.
