Basing one combo box on another

By Sal Ricciardi

Making choices is what a combo box is for, but sometimes one choice depends on another. In this article, you'll learn how to set up one combo box so that its list is based on the selection you make in another. We provide an example in a sample database that you can download.

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

Question: How do I create a combo box that limits its list based on the selection made in another combo box?    

You base the second combo box (combo box: A control used on a form that provides the combined functionality of a list box and a text box. You can type a value in a combo box, or you can click the control to display a list and then select an item from that list.) on a query that matches rows containing the value selected in the first combo box. You'll also need to place some Microsoft Visual Basic for Applications (VBA) code in certain form and control events (event: An action recognized by an object, such as a mouse click or key press, for which you can define a response. An event can be caused by a user action or a Visual Basic statement, or it can be triggered by the system.). The code makes sure that the combo boxes display the right values when certain things happen — such as when you make a choice in the first combo box, or when you first open the form. First, let's start by reviewing the RowSource and RowSourceType properties (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).).

Introducing RowSource and RowSourceType

The RowSource property lets you define the source for the items displayed in a combo box. You use it along with the RowSourceType property to tell Access how to populate the combo box. For example, you can set the RowSourceType property to Table/Query, and then set the RowSource property to the name of a query. Access then runs that query and uses the results to populate the combo box.

Property sheet showing Row Source and Row Source Type properties

You don't have to use a saved query. With the RowSourceType property set to Table/Query, you can create a query that exists as a SQL SELECT statement in the RowSource property of the combo box. This is the technique employed in the example form in this article's sample database.

The possible settings for the RowSourceType property are listed in the following table.

Setting Description
Table/Query The data is drawn from a table, query, or SQL statement specified by the RowSource property. This is the default setting.
Value List The data is drawn from a list of items specified by the RowSource property.
Field List The data is drawn from a list of field names from a table, query, or SQL statement specified by the RowSource property.

Most of the time, you'll use the Table/Query setting, which is the default.

Creating the example form

You can download the sample database for this article. In the database, you'll find an example form that demonstrates the technique explained here. To create the example form yourself, fire up Access and follow the steps in the following expandable section, then continue with the remainder of the article.

ShowStep-by-step: How to create the example form

Do one of the following:

ShowTo create a new form

  1. In the Database window, under Objects, click Forms.
  2. In the Database window, on the toolbar, click New.
  3. In the New Form dialog box, click Design View, and then click OK.

ShowTo open an existing form in Design view

  1. In the Database window, under Objects, click Forms.
  2. Click the form, and then click Design in the Database window.

To add the Category combo box    

  1. In the toolbox, click the Control Wizards button so that it appears selected.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. In the toolbox, click the Combo Box tool.
  2. Click and drag inside the design grid of the form to create the combo box.
  3. In the Combo Box Wizard, click the button next to I want the combo box to look up the values in a table or query, and then click Next.
  4. In the View section, click Tables.
  5. Click Table: Categories in the list, and then click Next.
  6. In Available Fields, double-click both CategoryID and CategoryName so that they both appear in Selected Fields, and then click Next.
  7. On the What sort order do you want for your list? page, click CategoryName in the first list, and then click Next.
  8. On the next page, make sure that the Hide key column (recommended) check box is selected, and then click Next.
  9. Type a label for your combo box. For the purposes of this article, I used Category Name:.
  10. Click Finish.
  11. Save the form.

To assign the Name property    

  1. With the form still open in Design view, click the new combo box.
  2. On the View menu, click Properties.
  3. In the Name box, type Category
  4. Close the query property sheet.

To add the Product combo box    

  1. In the toolbox, ensure that the Control Wizards button is selected.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. In the toolbox, click the Combo Box tool.
  2. Click and drag inside the design grid of the form to create the combo box.
  3. In the Combo Box Wizard, click the button next to I want the combo box to look up the values in a table or query, and then click Next.
  4. In the View section, click Tables.
  5. Click Table: Products in the list, and then click Next.
  6. In Available Fields, double-click ProductID, ProductName, and CategoryID so that all three appear in Selected Fields, and then click Next.
  7. On the What sort order do you want for your list? page, click ProductName in the first list, and then click Next.
  8. Make sure that the Hide key column (recommended) check box is selected, and then click and drag the right border of the Category column to shrink the column until it disappears. Click Next.
  9. Type a label for your combo box. For the purposes of this article, I used Product Name:.
  10. Click Finish.
  11. Save the form.

To assign the Name property    

  1. Click the Product combo box.
  2. On the View menu, click Properties.
  3. In the Name box, type Product.
  4. Close the property sheet.

To add a status text box    

  1. In the toolbox, click the Text Box tool.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. Click and drag below the two combo boxes inside the design grid of the form to create the text box.
  2. On the View menu, click Properties.
  3. In the Control Source box, carefully type
    ="You chose the " & Product.Column(1) & " product in the " & Category.Column(1) & " category!"
  4. Close the property sheet.
  5. Delete the label associated with the text box that you just created.

To add an optional explanatory text box    

  1. In the toolbox, click the Text Box tool.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. Click and drag below the two combo boxes inside the design grid of the form to create the text box. In the example form, I placed this text box between the two combo boxes — above the Product combo box and below the Category combo box.
  2. On the View menu, click Properties.
  3. In the Control Source box, carefully type
    ="When you choose a category, the Products list changes to offer only the products from that category."
  4. Close the property sheet.
  5. Delete the label associated with the text box that you just created.
  6. Save the form.

To format the text boxes (optional)    

  1. Increase the font size and change the color of the font.
  2. Resize and move each text box to fit the text.
  3. Set the BackStyle property to Transparent so that the background of the text box matches that of the form.

Be sure to follow the remaining steps in this article to add criteria to the Product combo box and to include code in the appropriate places to, among other things, keep the combo boxes updated.

How to add criteria to the Product query

Because you can have Access populate a combo box with the results of a query, you can also add criteria to that query to limit what appears in the combo box. In our case, we want the criteria for the second combo box to limit the results to only those rows that match the value in the first combo box.

For example, suppose you want to have a category combo box named Category and a product combo box named Product. When you click the Category combo box, you want the Product combo box to list only the products in that category. You accomplish this by placing criteria in the query for the Product combo box that matches the current value in the Category combo box.

To add criteria to the query    

  1. Open the property sheet for the Product combo box.
  2. Click the Row Source box, and then click the ellipsis button (...) that appears.
  3. In the Query Builder, make sure the field grid lists the columns from the Products table that you want to appear in the Product combo box, such as ProductID, ProductName, and so on. Be sure to include the CategoryID field.
  4. In the CategoryId column in the Query Builder grid, type [Forms]![Combo Form]![Category] in the Criteria row, replacing "Combo Form" with the name of your form. The criteria tell Access to restrict the query results to only those rows that match the value currently selected in the Category combo box. Your Query Builder grid might now look something like the one in the following illustration.

Query Builder criteria for linking combo boxes

  1. Finally, close the Query Builder and then close the query property sheet, clicking Yes when Access prompts you to save the changes.

Access inserts a SQL statement into the RowSource property when you close the Query Builder. The SQL statement includes a WHERE clause similar to the following one.

WHERE (((Products.CategoryID)=
       [Forms]![Combo Form]![Category]))

The WHERE clause restricts the rows to only those that match the value in the Category combo box.

  1. Save the form.

Understanding the BoundColumn and ColumnCount properties

One of the nice things about using the Combo Box Wizard is that it automatically populates the properties you need. Two of these properties merit further explanation.

  • The BoundColumn property tells Access which column in the combo box holds the value that is to be associated with the control. This property holds a number that represents a column in the row source, starting with 1 and progressing to 2, 3, and so on, from left to right. Usually, you select a column that holds a primary key, like CategoryID or ProductID. That way, if you reference the value of the control, you get the value of the CategoryID or ProductID field for the selected row.

For example, the expression [Forms]![Combo Form]![Category] (entered in the Query Builder grid shown in the preceding illustration) returns the value stored in the BoundColumn property of the selected row in the Category combo box control. The value in the CategoryID field is returned because the BoundColumn property of the Category combo box is set to 1, which represents the CategoryID column. The CategoryID value is then used by the query to match rows for the Product combo box.

  • The ColumnCount property tells Access how many columns there are in the combo box. For instance, for the Category combo box, you set this property value to 2 because there are two columns in the row source: CategoryID and CategoryName. For the Product combo box, you set the ColumnCount property to 3, because the row source includes the ProductID, ProductName, and CategoryID columns.

Note that when computing a value for ColumnCount, you must count all columns whose value is to be made available in Access, whether the column is actually visible in the combo box or not. You can hide a column by using the ColumnWidths property.

For more information, see the Hiding some columns section.

How to keep the combo box updated

The Product combo box must be updated whenever the value in the Category combo box changes. To do this, you can add VBA code to requery the row source for the Product combo box whenever the Category combo box is updated. Access provides the AfterUpdate event for just this purpose.

Let's take a look at the code for the AfterUpdate event. First, we set the value of the Product combo box to Null, which has the effect of ensuring that nothing initially appears in the box. This is a good idea, because once you choose a category, the value currently selected in the Product combo box becomes invalid — because the product currently displayed belongs to the previous category.

Next, the Requery method is called to repopulate the Product combo box, based on the current category. Finally, the Product combo box is preset to the first item in the list by using the ItemData property. The ItemData property returns the value for the specified row in a combo box or list box. Because the list is zero-based, remember to choose 0 as the index for the first row, and not 1.

Private Sub CategoryAfterUpdate()
  Me.Product = Null
  Me.Product.Requery
  Me.Product = Me.Product.ItemData(0)
End Sub

For detailed step-by-step instructions about how to place the code in the AfterUpdate event of the Category combo box, see the following expandable section.

ShowStep-by-step: How to place code in the first combo box to automatically update the second combo box

To create the code, do the following:

To open the form in Design view    

  1. In the Database window, under Objects, click Forms.
  2. Click the form, and then click Design in the Database window.

To edit the Category_AfterUpdate procedure    

  1. Click the Category combo box to select it.
  2. On the View menu, click Properties, and then click the Event tab.
  3. Click the After Update box, and then click the ellipsis button (...) when it appears.
  4. When the Choose Builder dialog box appears, click Code Builder, and then click OK.
  5. Enter code in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) so that the Category_AfterUpdate procedure appears exactly as shown in the preceding example.
  6. When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Office Access.
  7. Close the property sheet.
  8. Close the form.

What to do when you open the form

When you first open the form, you might find that the combo boxes show no values because you haven't chosen any yet. For the example form, I decided that it would be better to display the first value in the category list and the first product of that category, so I placed the following VBA code in the form's Load event. The code is called when you first open the form. It sets the category to the first value in the list, and then calls the Category_AfterUpdate procedure to update the Product combo box.

Private Sub Form_Load(Cancel As Integer)
  Me.Category = Me.Category.ItemData(0)
  Call Category_AfterUpdate
End Sub

The example form uses unbound (unbound control: A control that is not connected to a field in an underlying table, query, or SQL statement. An unbound control is often used to display informational text or decorative pictures.) combo boxes. An unbound combo box is one that is not linked to a field in a table or query. If you use bound (bound column: The column in a list box, combo box, or drop-down list box that's bound to the field specified by the control's ControlSource property.) combo boxes instead, you probably won't want to use the Form_Load procedure shown above, because it resets the Category every time the form loads. Instead, eliminate that code or use something like the following instead:

Private Sub Form_Load(Cancel As Integer)
  If IsNull(Category) Then
    Me.Category = Me.Category.ItemData(0)
    Call Category_AfterUpdate
  End If
End Sub

This version only resets the category if it's empty.

For detailed step-by-step instructions about how to place the code in the Load event of the form, see the following expandable section.

ShowStep-by-step: How to place code in the form's Load event that will run when the form is opened

To create the code, perform the following steps:

To open the form in Design view    

  1. In the Database window, under Objects, click Forms.
  2. Click the form, and then click Design in the Database window.

To edit the Form_Load procedure    

  1. On the Edit menu, click Select Form.
  2. On the View menu, click Properties, and then click the Event tab.
  3. Click the On Load box, and then click the ellipsis button (...) when it appears.
  4. When the Choose Builder dialog box appears, click Code Builder, and then click OK.
  5. Enter the code in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) so that the Form_Load procedure appears exactly as shown in the preceding example.
  6. When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Office Access.
  7. Close the property sheet.
  8. Close the form.

How to handle record navigation

If you use bound combo boxes, you can still use the technique shown here, but you'll need to consider what you want to happen when the current record changes due to the user navigating between records. In that case, you may need to requery the second combo box whenever you navigate from record to record. The proper place to do this is in the Current event of the form, using code similar to the following example.

Private Sub Form_Current()
  Me.Product.Requery
End Sub

For detailed step-by-step instructions about how to place the code in the Current event of the form, see the following expandable section.

ShowStep-by-step: How to place code in the form's Current event that will run when you navigate from one record to another

To create the code, perform the following steps:

To open the form in Design view    

  1. In the Database window, under Objects, click Forms.
  2. Click the form, and then click Design in the Database window.

To edit the Form_Current procedure    

  1. On the Edit menu, click Select Form.
  2. On the View menu, click Properties, and then click the Event tab.
  3. Click the On Current box, and then click the ellipsis button (...) when it appears.
  4. When the Choose Builder dialog box appears, click Code Builder, and then click OK.
  5. Enter the code in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) so that the Form_Current procedure appears exactly as shown in the preceding example.
  6. When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Office Access.
  7. Close the property sheet.
  8. Close the form.

Hiding some columns

When you create the Product combo box, you'll want to include (at the minimum) the ProductID, ProductName, and CategoryID columns — but you'll probably want to display only the ProductName column. To hide the columns that you don't want to display, you can set the ColumnWidths property of the Product combo box, shown in the following illustration.

Column Widths property in the Product combo box

The ColumnWidths property lets you specify the width of each column in your combo box in inches (or in centimeters, if you're using the Metric measurement system). You separate the column measurements by using semicolons. For columns that you want to hide, set the width to 0.

Note that setting the column width to zero hides the column from view, though the column still exists and its values are still available to Access. Thus, in the previous illustration for the Product combo box, the ProductID and CategoryID columns are hidden, but the values remain available. In fact, Access supplies the Column property so that you can obtain the current value of any column in the selected row in a combo box. For instance, Product.Column(0) returns the value in the ProductID column, Product.Column(1) returns the ProductName, and Product.Column(2) returns the CategoryID.

Download the sample database

The sample database includes the example form discussed here, in addition to sample data drawn from the Northwind.mdb sample database that is included with Access.

For more information

 
 
Applies to:
Access 2003