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 ora report, even when those fields come from a table that is not bound to theform 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: Use AutoLookup in forms.
Method 2: Use the Column property of a multiple-column combo box to update a text box control with new information.
Method 3: Use multiple DLookup() functions in forms and reports.
Method 1: Using AutoLookup in Forms
You 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:
The query must be based on more than one table and the tables must have a one-to-many relationship
The join field on the "one" side of the relationship must have a unique index.
The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key for that table.) For AutoLookup to work, this field cannot be a primary key and its Indexed property cannot be sent to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the ID from the Customers table.
The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
When you update the data:
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 Box
By 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:
Open the sample Northwind Template database.
Create a form or report based on the appropriate table or query.
Add a combo box or list box that retrieves information from more thanone field.
For example, you might use the following multiple-column Select statement as the RowSource property for a combo box or list box to display information from several columns in the Categories table:
SELECT [Employees].[ID], [Employees].[Last Name], [Employees].[Job Title] FROM Employees ORDER BY [Employees].[Last Name]
For this example, set the ColumnCount property to 3, and set the ColumnWidths property to an appropriate size for the combo box or list box. After you have the combo box or list box sized correctly anddefined to return multiple fields, you can use the Column property to display the current selection in a text box control. Choose one entry in the list box first; otherwise, it returns a Null.
The Column property uses a reference argument to refer to a specific column in the multiple-column combo box or list box. Use Column(0) to refer to the first column, Column(1) to refer to the second column, and so on.
This example uses Column(1) to refer to [Last Name], the second column in the combo box.
To display the [Last Name] column of the current combo box selection, create a text box control. Make the text box a calculated control by defining the following expression as the ControlSource for the text box
where cboControlName is the name of the combo box. The Column property makes the text box (calculated control) read-only.
Method 3: Using DLookup in Controls
Create an unbound form in the Northwind sample database, and then add a combo box named cboEmployeeLookup and a text box called txtJobTitleLookup.
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])
Note that when you select an Employee value with the cboEmployeeLookup combo box, the txtJobTitleLookup text box is filled in.