DataWright Information Services

Consulting and Resources for Excel and Access




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.

Combo box techniques

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

cmbSuppliers query

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

cmbProducts query

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:
cmbSupplierSearch query

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.

the modified Suppliers form

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
the modified Orders form