How to Automatically Populate Customer Details in Microsoft Access Forms
Question details
The user wants to automatically display a customer's name, city, and state on a form when a specific customer code is selected.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Entering records into a Trips table using a form and needing related customer information to autofill based on the selected Customer Code.
- Observed behavior
- The goal state is to dynamically display related customer fields without manually typing them and without violating database normalization rules.
Ensure your database relationships are properly established and that you only store the foreign key (CustomerCode) in your Trips table to prevent data duplication.
Use Combo Box Column Property with VBA
Use a combo box to select the Customer Code and trigger VBA code in the After Update event to fill the corresponding text boxes.
This is the most common method for single-record forms. By referencing the hidden columns of a combo box, you can push data into unbound or locked text boxes without querying the database a second time.
Add a combo box to your form bound to the CustomerCode field. Set its Row Source to include CustomerCode, CustomerName, City, and State.
In the Property Sheet, set the Column Count to 4. To hide the extra columns from the user, set the Column Widths property to something like '1in;0in;0in;0in'.
Add unbound text boxes for Name, City, and State on the form. Name them txtCustomerName, txtCity, and txtState, and set their Locked property to Yes to make them read-only.
Click the ellipsis (...) next to the On After Update event of the combo box. Enter code assigning the column values to the text boxes, for example: Me.txtCustomerName = Me.cboCustomerCode.Column(1).

Use a Joined Query as the Record Source
Base your form on a query that joins the Trips and Customers tables, allowing related fields to display automatically without VBA.
Need a Lightweight Data Management Solution? Try WPS Office
While Microsoft Access is designed for complex relational databases, many data tracking and autofill tasks can be easily handled using spreadsheets. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Word, Excel, and PowerPoint, featuring powerful functions like VLOOKUP for automatic data population.
- 1. Create your data tables: Open WPS Spreadsheet and create two sheets: 'Trips' for your main data entry and 'Customers' for your master list.
- 2. List customer details: In the 'Customers' sheet, list your Customer Codes, Names, Cities, and States in columns A through D.
- 3. Use VLOOKUP to auto-populate: In the 'Trips' sheet, enter the formula =VLOOKUP(A2, Customers!A:D, 2, FALSE) in the Customer Name column to automatically pull data based on the entered code.

Frequently Asked Questions
Why shouldn't I save the customer name directly into the Trips table?
Storing the name, city, and state directly in the Trips table violates database normalization. It duplicates data, increasing database size and creating update anomalies—meaning if a customer changes their name, you would have to update multiple records instead of just one central record.
How do I hide the extra columns in my Access combo box dropdown?
You can hide columns in an Access combo box by adjusting the Column Widths property in the Property Sheet. For instance, setting it to '1in;0in;0in;0in' will display the first column at 1 inch wide while hiding the remaining columns, yet keeping their data available for VBA to reference.
Why are my text boxes remaining blank after selecting a code?
This commonly happens if the combo box's Column Count property is smaller than the number of fields in the Row Source, or if your VBA column index is incorrect. Ensure the Column Count matches the number of fields in the query and remember that VBA Column indexing starts at 0.
Can I use the VBA After Update method on a Continuous Form?
It is not recommended. Using unbound text boxes populated via VBA on a continuous form will cause the text boxes for all visible rows to display the value of the currently active row. For continuous forms, you should base the form on a joined query instead.




