ACC: Using Column Property of Combo Box to Update Text Box

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

You can display multiple fields in a combo box or list box on a form or report, even when those fields come from a table that is not bound to the form or report. You can also update controls such as text boxes with new information based on what a user selects from a combo box or list box.

MORE INFORMATION

To accomplish these objectives, 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 as focus moves from row to row in the combo box.
• Method 3: Use multiple DLookup() functions in forms and reports.

Method 1: Using AutoLookup in Forms

For more information about using AutoLookup in forms, type "AutoLookup" in the Office Assistant, click Search, and then click to view "Create an AutoLookup query that automatically fills in 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. The following example demonstrates how to do this:

1. Create a form or report based on the appropriate table or query.

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

       For Microsoft Access 97 and 7.0, use:
   

         "Select [CategoryId], [CategoryName], [Description] from
             Categories Order by [CategoryName];"
   
       For Microsoft Access versions 1.x and 2.0, use:
   
         Select [Category Id], [Category Name], [Description] from
            Categories Order by [Category 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 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.
   
   

3. To display the [CategoryName] 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

         =[<cboControlName>].Column(1)
   

   where <cboControlName> is the name of the combo box. The Column property will make the text box (calculated control) read-only.

Method 3: Using DLookup in Controls

1. Create an unbound form and place a combo box on it named cboEmployeeLookup, and a text box called LastName.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

         Object: Combo Box
         -------------------------------
         Name: cboEmployeeLookup
         RowSourceType: Table/Query
         RowSource:     Categories
         ColumnCount:   2
         ColumnWidths:  1
         BoundColumn:   1
   
         Object: Text Box
         -------------------------------
         Name: LastNameLookup
         ControlSource: =DLookup("[LastName]","Employees","[EmployeeID]=" _
            & [cboEmployeeLookup])
   
   

REFERENCES

For more information about Column property, type "column property" in the Office Assistant, click Search, and then click to view "Column Property."

For more information about the DLookup() function, type "DLookup" in the Office Assistant, click Search, and then click to view "DLookup Function."

Keywords          : FmsCmbo kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo