Microsoft KB Archive/209738

= ACC2000: How to Use the Column Property of a Combo Box to Update a Text Box =

Article ID: 209738

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209738



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
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.



MORE INFORMATION
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
For more information about using AutoLookup in forms, click Microsoft Access Help on the Help menu, type create an autolookup query that automatically fills in data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

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.mdb or NorthwindCS.adp 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 than one 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 [CategoryId], [CategoryName], [Description] from Categories Order by [CategoryName]

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 and defined 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 [CategoryName], the second column in the combo box. To display the [CategoryName] 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

=[ ].Column(1)

where  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, and then add a combo box named cboEmployeeLookup and a text box called txtLastNameLookup.   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]) Note that when you select an EmployeeID value with the cboEmployeeLookup combo box, the txtLastNameLookup text box is filled in.

