Using combo boxes

Combo and list boxes are among the most powerful and versatile controls in Access. They allow you to control data entry by limiting the options to existing items, but you can do much more than that. The articles on this page will demonstrate some of the things you can do with combo boxes.

Creating a combo box

To create a combo box, you can do one of the following (in Design view on a form or report):

• Select the combo box icon in the Tools menu, and click on the form
• Right-click an existing text box or list box, and Change To > Combo Box

Important combo box properties

If you go to the All tab of a combo box's properties, you will see a long list. Some of these properties will be used every time you setup a combo box; they are listed below.

Property	Comment
Control Source	The table field that your combo box is bound to. If you are using the combo box for searching or filtering, this will be left blank
Row Source Type	This can have 3 values: Value List, Table / Query, or the name of a special function that populates the combo box. Value lists are convenient if you have a restricted number of options (Say, the States of Australia). Using a table or query as the row source means that you can add more items to the combo box by adding items to the underlying table. If you use a value list, you must add the items directly to the combo box's value list; you can do this using VBA if required
Row Source	This is the data that you will see in the combo box. If you have selected a custom function, it will be left blank; otherwise, you will see a comma-delimited item list or a SQL statement representing the underlying query
Column Count	The number of columns in the query or value list
Column Widths	Here, you define how wide each column will be. To hide a column set its width to 0. The values that you enter here will depend on your regional settings: inches for US, centimetres for most other countries
List Width	Normally this is Auto, meaning that the drop-down list will be the same as the width of the combo box. But if the sum of the visible column widths is 10 cm and you want to see all of the detail, set this to 10 cm too. Bottom line: match the List Width to the sum of the Column Widths if you want to see the full width of the drop-down list
Limit to List	By default this is No. Set it to Yes if you want to force users to use only the items in the current row source. You will then need to use code to let them add the new item on the fly

Cascading combo boxes: filtering one combo on the basis of another combo box

It is often useful to be able to select a category from one combo box and then select items belonging to that category, using a second combo box. Setting this up requires 3 steps, which are detailed below, using the Northwind sample database.

Step 1: Create the first combo box

In the Northwind database, create a new form and call it frmChooseProducts. Add 2 combo boxes to the form; name the first cmbSuppliers, and the second cmbProducts. Set the properties of cmbSuppliers as follows:

Property	Value
Name	cmbSuppliers
Column Count	2
Bound Column	1
Column Widths	0cm;5cm
Row Source	SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;

Click the thumbnail to see the query grid for cmbSuppliers

Step 2: Create the second combo box

Set the properties of cmbProducts as follows:

Property	Value
Name	cmbProducts
Column Count	3
Bound Column	1
Column Widths	0cm;5cm;0cm
Row Source	SELECT Products.ProductID, Products.ProductName, Products.SupplierID FROM Products WHERE (((Products.SupplierID)=[Forms]![frmChooseProducts]![cmbSupplier]));

Click the thumbnail to see the query grid for cmbProducts

Step 3: Add code to the first combo box that re-queries the second combo box

Now, go back to cmbSuppliers. In the properties, select the Events tab and create the following code in the After Update event of the cmbSuppliers (check HERE if you don't know how to create an event procedure):

Private Sub cmbSupplier_AfterUpdate()
  Me.cmbProducts.Requery
End Sub

The After Update event is triggered once you have made a selection in the combo box; the Requery method re-evaluates the SQL behind cmbProducts, so that the combo displays the appropriate list when the user selects the drop-down. Save the form, switch to Form view, and give it a try.

Update: A newer article shows a technique that works on continuous forms.

Letting users choose any item from the second combo box

Sometimes you want to switch between having the second combo box dependent on the first, and being able to select any value. This tip shows one way to achieve that -- by modifying the Row Source of the second combo. Change the AfterUpdate event for cmbSupplier to this:

Private Sub Supplier_AfterUpdate()
  If IsNull(Me.Supplier) Then
    Me.cmbProducts.RowSource = "SELECT Products.ProductID, Products.ProductName, " _
        & "Products.SupplierID FROM Products"
  Else
    Me.cmbProducts.RowSource = "SELECT Products.ProductID, Products.ProductName, " _
        & "Products.SupplierID FROM Products " _
        & "WHERE (((Products.SupplierID)=[Forms]![frmChooseProducts]![cmbSupplier]));"
  End If
  Me.cmbProducts.Requery
End Sub

If you clear cmbSuppliers, the above code removes the WHERE clause that makes cmbProducts dependent on cmbSuppliers. If cmbSuppliers has anything in it, the code resets cmbProducts to make it dependent on cmbSuppliers again.

For the user, this means that you can decide whether or not to make the second combo box dependent on the first.

Finding or filtering records with a combo box

On a data entry form with many records, you may need to find a unique record or filter for all records matching a particular value. in both cases you can use a combo box to narrow down the search item. The next two articles show how to do this.

Finding a unique record

This example uses the Suppliers form in the Northwind sample database. When we make a selection from the combo, we will move to the selected supplier without filtering the form. Hence, this technique allows you to continue browsing the rest of the form's records without having to remove a filter. Do the following:

• Open the Suppliers form in Design view
• Open the Form Header to a height of 1cm, and add a new combo box to the header. Set up the combo box with these properties:
• Note -- the combo box is unbound (does not have a Control Source) because it is used for searching, not for data entry.

Property	Value
Name	cmbSupplierSearch
Column Count	2
Bound Column	1
Column Widths	0cm;6cm
Row Source	SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
Width	6cm
Label Caption	Supplier Search

The query grid for cmbSupplierSearch looks like this:

To navigate to the correct location, we need to move to the record whose ID matches the selected supplier. We can do this by using the record's bookmark in the form's recordset. The following code goes into the After Update event of the combo, which is triggered when you make a selection from the list.

Private Sub cmbSupplierSearch_AfterUpdate()
  '' ===========================================================
  '' This sub allows you to go to a specific supplier instead of
  '' having to browse records.
  '' Requires a reference to the Microsoft DAO 3.6 Object Library
  '' Created by: Denis Wright
  '' Creation date: 19 Sep 2007
  '' ===========================================================

  'declare variables
  Dim rstForm As DAO.Recordset

  'the recordsetclone is a copy of the form's recordset.
  'by synchronising the combo selection with the recordsetclone,
  'and navigating to the corresponding bookmark, we navigate
  'to the desired record.

  Set rstForm = Me.RecordsetClone
  With rstForm
    .FindFirst "[SupplierID] = " & Me.cmbSupplierSearch
  End With

  'go to the selected supplier's record
  Me.Bookmark = rstForm.Bookmark
End Sub

For a tip on creating event code in Access, see this page

Click the thumbnail to see the finished form. When you select a supplier, you navigate to that record.

Filtering for records that match a particular value

The Find technique works well if the record is unique. But if you need to see all orders from a particular client, a filter is required. This technique filters the Orders form in the Northwind database.

• Open Orders in Design view. View > Form Header/Footer to display the header and footer; set the height of the header to 1cm, and the height of the footer to 0.
• Add a combo box to the form header. This combo will also be unbound, and needs to have the properties:

Property	Value
Name	cmbFindCustomer
Column Count	2
Bound Column	1
Column Widths	0cm;6cm
Row Source	SELECT Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;
Width	6cm
Label Caption	Show all orders for:

Now to add the code that filters the form. It will go in the AfterUpdate event of the combo box:

Private Sub cmbFindCustomer_AfterUpdate()
  ''==========================================================
  ''Making a selection from this control filters the Orders form
  ''for orders by the selected customer.
  ''Created 19 Sep 2007 by Denis Wright
  ''==========================================================
  'declare variables
  Dim sFilter As String

  'in this case, the ID is text so the ID value
  'needs to be wrapped in single quotes.
  sFilter = "[CustomerID]= '" & Me.cmbFindCustomer & "'"

  'assign the filter value,and turn filtering on
  Me.Filter = sFilter
  Me.FilterOn = True
End Sub

Click the thumbnail to see the completed form

  Home | About | Links | Contact | Excel | Access