How to Use One Access Combo Box to Populate Two Fields
Question details
The user needs to use a single combo box selection in Microsoft Access to automatically populate another control on the same form, without storing redundant duplicate data in the underlying database.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a data entry form where selecting an item from a drop-down list (combo box) should autofill related information into an adjacent text box or field.
- Observed behavior
- By default, a combo box is bound to only one field in a table. Users need a method to extract additional columns from the combo box selection to populate other controls correctly.
Ensure your Microsoft Access database is backed up before adding VBA code, and verify that the Row Source query for your combo box includes all the columns you wish to display.
Use the AfterUpdate VBA Event to Populate Controls
Use a simple VBA script on the combo box's AfterUpdate event to copy data from a hidden column into a target text box.
A multicolumn combo box can hold several pieces of related data. By referencing the Column property in VBA, you can extract this data when a user makes a selection and pass it to another control.
Right-click your form in the navigation pane and select Design View.
Select your combo box, open the Property Sheet, and ensure the Row Source query includes the extra column you need to extract.
In the Property Sheet, click the Event tab. Click the ellipsis (...) next to the On After Update property to open the VBA Editor.
Type the code to assign the column value to your target control. For example: Me.txtTargetControl = Me.cboSourceControl.Column(1). Remember that column indexes are zero-based.
Save the VBA module, switch your form back to Form View, and test the combo box to ensure the second field updates automatically.
Use Unbound Controls for Database Normalization
Instead of saving the copied value into a new table field, store only the foreign key and use an unbound text box to display the related information.
Experience a Lighter, Faster Alternative for Your Office Tasks
While Microsoft Access is a robust tool for relational databases, everyday data tracking, forms, and lists can often be managed more efficiently in spreadsheets. WPS Office provides a free, highly compatible alternative to Microsoft Office, making data management simpler without a steep learning curve.
- 1. Download WPS Office: Visit the official WPS website to download and install the free suite.
- 2. Open Your Data: Launch WPS Spreadsheet and easily open your existing .xlsx or .csv files.
- 3. Manage Data Easily: Use built-in data validation tools to create dependent drop-down lists without needing complex VBA code.
Frequently Asked Questions
Why doesn't the second field update when I select an item in the combo box?
This usually happens if the target control is unbound and hasn't been forced to refresh, or if the VBA AfterUpdate event isn't firing. Check the Property Sheet to ensure the event procedure is properly linked to the combo box.
Does Column(1) refer to the first column in my combo box?
No, Microsoft Access combo box columns are zero-based when referenced in VBA. Column(0) is the first column, Column(1) is the second column, and so forth.
Is it bad practice to copy related data to another table?
Yes, copying related information (such as an employee's department) into a new table when you already store the Employee ID creates redundant data. This violates database normalization rules. It is better to store only the foreign key and use unbound controls or queries to display the related data.
