Article ID: 209738 - View products that this article applies to.
This article was previously published under Q209738
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access desktop database (.mdb and .accdb) and to a Microsoft Access project (.adp).
You can display multiple fields in a combo box or a list box on a form or a report, even when those fields come from a table that is not bound to the form or to the report. You can also update controls, such as text boxes, with new information based on what a user selects from a combo box or a list box.
To accomplish the tasks mentioned in the "Summary" section, use one of these techniques:
Method 1: Using AutoLookup in FormsYou can design a multiple-table query to automatically fill in certain field values for a new record and use the query as the row source for a combo box. When you enter a value in the join field in the query, or in a form or report based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field between a Customers table and an Orders table (typically a customer identifier such as a Customer ID), you could enter the Customer ID and have Access enter the rest of the information for that customer. If no matching information is found, Access displays an error message when the focus leaves the record. For AutoLookup to work, certain conditions must be met:
When the value of the join field from the "many" side of the relationship is added or changed in a record, Access automatically finds and displays the associated values from the table on the "one" side of the relationship.
You can always update the join field from the "many" side of a relationship, but you can update the join field from the "one" side only if you enabled cascading updates when defining the relationship between the tables. Either way, when you update data, Access automatically recalculates any totals or expressions in the query that is dependent on the updated data.
Method 2: Using the Column Property of a Combo BoxBy assigning the Column property of a multiple-column combo box or list box to a text box, you can display one column from the current combo box selection in the text box. Microsoft Access automatically updates the text box when a selection is made from the combo box. To do this, follow these steps:
Method 3: Using DLookup in ControlsCreate an unbound form in the Northwind sample database, and then add a combo box named cboEmployeeLookup and a text box called txtJobTitleLookup.
Note that when you select an Employee value with the cboEmployeeLookup combo box, the txtJobTitleLookup text box is filled in.
In Northwind.mdb in Access 2003 or earlier: Object: Combo Box ------------------------------- Name: cboEmployeeLookup RowSourceType: Table/Query ("Table/View/StoredProc" in Access project) RowSource: Employees ColumnCount: 2 ColumnWidths: 1 BoundColumn: 1 DefaultValue: 1 Object: Text Box ------------------------------- Name: txtLastNameLookup ControlSource: =DLookup("[LastName]","Employees","[EmployeeID]=" & [cboEmployeeLookup]) In the Northwind Template database in Access 2007 or Access 2010: Object: Combo Box ------------------------------- Name: cboEmployeeLookup RowSourceType: Table/Query ("Table/View/StoredProc" in Access project) RowSource: Employees ColumnCount: 3 ColumnWidths: 0";0";1" BoundColumn: 1 DefaultValue: 1 Object: Text Box ------------------------------- Name: txtJobTitleLookup ControlSource: =DLookup("[Job Title]","Employees","[ID]=" & [cboEmployeeLookup])
Article ID: 209738 - Last Review: September 27, 2013 - Revision: 3.0