Others

How to Use Lookup Fields and Combo Boxes in Microsoft Access

Kushani Nimanthika Sep 25, 2026 869 views

Question details

The user wants to select a PEIMS course code using a combo box and automatically display its corresponding course title without duplicating data.

How to Use Lookup Fields and Combo Boxes in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a database form to select a code and display related descriptive details using relational data.
Observed behavior: Instead of duplicating text titles, the goal is to store IDs or codes in the main table and use a read-only text box to dynamically display the title using lookup fields or combo boxes.

Before you start

Ensure you have your course codes and titles stored in a separate, related table (e.g., a 'PEIMS' table) before configuring the combo box on your form.

Solution 1Recommended

Configure a Form Combo Box and Read-Only Text Box

Use a combo box on your form to select a code and a read-only text box to display the corresponding title, ensuring database normalization best practices.

In a well-designed relational database, you should store the course title in one dedicated table (e.g., PEIMS) and display it wherever the course code is used. It is highly recommended to avoid using lookup fields directly in your tables and instead implement combo boxes at the form level.

Create a Related Table

Ensure you have a separate table (e.g., 'PEIMS') that contains at least two fields: CourseCode (or CourseID) and CourseTitle.

Add a Combo Box to Your Form

Open your form in Design View. Add a combo box control and set its Row Source property to a SQL statement such as: SELECT CourseCode, CourseTitle FROM PEIMS;

Configure the Bound Column

Ensure the combo box is set to store (bind) the course code or ID, typically by setting the Bound Column property to 1 in the Property Sheet.

Add a Read-Only Text Box

Add a text box to your form to display the title. Set its Control Source property to =cboCourse.Column(1) (assuming your combo box is named cboCourse).

Lock the Text Box

Select the newly added text box, go to the Property Sheet under the Data tab, and set 'Locked' to Yes to make it read-only.

Configure a Form Combo Box and Read-Only Text Box

Object Naming Best Practice: Always avoid spaces in your table, form, and control names. Use CamelCase (e.g., CourseTitle) or underscores to prevent syntax errors and coding issues.

Free Microsoft Office alternative

Manage Data Easily with WPS Office

While Microsoft Access handles complex relational databases, WPS Spreadsheet offers an excellent, lightweight alternative for data management, lookups, and list tracking without the steep learning curve. It provides a free, familiar interface with high format compatibility for your everyday office needs.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and follow the simple installation instructions.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your existing data list or create a new workbook.
3. Create a Drop-Down List: Go to the Data tab, select Data Validation, and choose List to create a combo box equivalent for your codes.
4. Apply Lookup Functions: Use the VLOOKUP function in an adjacent cell to automatically display related titles based on your drop-down selection.

Free, lightweight, and user-friendly interfaceSeamless Microsoft Excel (.xlsx, .xls) format compatibilityRobust Data Validation tools for creating drop-down listsPowerful VLOOKUP functions for automatic data retrievalFamiliar UI allowing for a seamless migration from Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why should I avoid using lookup fields directly in Access tables?

Using lookup fields at the table level can mask the actual data being stored (e.g., showing a title while storing an ID), which often causes confusion when writing queries, running reports, or migrating data. It is widely considered best practice to use combo boxes on forms instead.

What does the Control Source =cboCourse.Column(1) mean?

In Microsoft Access, columns in a combo box are zero-indexed. This means Column(0) refers to the first field in your Row Source (like CourseCode), and Column(1) refers to the second field (like CourseTitle). This expression tells the text box to display the second column's value.

How can I automatically update the text box when the combo box selection changes?

Access forms typically handle this automatically via the control source reference. However, if it doesn't refresh immediately, you can add a simple VBA macro to the combo box's 'After Update' event with the code: Me.YourTextBoxName.Requery.

Office Quick Solutions