Microsoft Access: How to Use the Column Property of a Combo Box to Update a Text Box

Article translations Article translations
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).

Expand all | Collapse all

On This Page

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

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:
  1. Open the sample Northwind Template database.
  2. Create a form or report based on the appropriate table or query.
  3. 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 [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 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 [Last Name], the second column in the combo box.
  4. 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
    =[cboControlName].Column(1)
    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.

Properties

Article ID: 209738 - Last Review: September 27, 2013 - Revision: 3.0
Applies to
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 2002 Standard Edition
  • Microsoft Office Access 2003
  • Microsoft Office Access 2007
  • Microsoft Access 2010
  • Microsoft Access 2013
Keywords: 
kbhowto kbinfo kbusage KB209738

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com