Access -- Linked Combo Boxes

You are probably familiar with using a combo box to select a record in a form's record source. The RecordsetClone Help topic has an example showing how to use the form's Recordset Clone to synchronize the form to the selection in the combo box, and I use this technique very frequently, to allow the user to select a record for a form. The standard code I use to do form synchronization is shown below (in two variants, one for a numeric ID and one for a text field).

  Private Sub cboSelect_AfterUpdate()

     Dim strSearch As String

     'For text IDs
     strSearch = "[______ID] = " & Chr$(34) & Me![cboSelect] & Chr$(34)

     'For numeric IDs
     strSearch = "[______ID] = " & Me![cboSelect]

     'Find the record that matches the control.
     Me.RecordsetClone.FindFirst strSearch
     Me.Bookmark = Me.RecordsetClone.Bookmark

  End Sub

However, sometimes this technique is not enough. In a recent application, I needed to set up a form so the user could first select a customer, and then select a boat for that customer (each customer could have multiple boats). Just setting up a second combo box for selecting boats wouldn't do, because I needed the second combo box to display just the boats belonging to the customer selected in the first combo box. To accomplish this goal, I needed several things:

The second combo box (cboSelectBoat) had to be limited to just boats belonging to the customer selected in the first combo box (cboSelectCustomer). I did this with a criterion for the CustID field in cboSelectBoat's row source query: [Forms]![frmCustomersAndBoats]![cboSelectCustomer]

But that wasn't sufficient. I also needed to requery cboSelectBoat after selecting a new item in cboSelectCustomer, to change the list of boats displayed in cboSelectBoat. I did this with the line

Me![cboSelectBoat].Requery
in cboSelectCustomer's AfterUpdate event procedure.

Finally, I used the RecordsetClone technique in cboSelectBoat's AfterUpdate event procedure to synchronize with the correct record, using the two items selected from the two combo boxes:

  strSearch = "[CustID] = " & Me![cboSelectCustomer] _
     & " and [BoatID] = " & Chr(34) & Me![cboSelectBoat] & Chr(34)
  Debug.Print strSearch

Notes:
* CustID is a numeric field, while BoatID is a text field, and thus needs to be wrapped in quotes, using Chr(34) to avoid problems with nested quotes

* The Debug.Print line prints the search string to the Debug window (open it with Ctrl-G), which is helpful in case you are having problems with searching.

Using two linked combo boxes isn't the only way to do a two-step selection; in some cases, it may be more efficient to do the selection in a single multi-column combo box, set up to display all the fields the user needs to select the right record. I made a combo box with a query whose first column displayed the CustID field and the second displayed the BoatID field. Then I used the modified search string below to select the correct record based on the user's selection:

  strSearch = "[CustID] = " & Me![cboSelect].Column(0) _
     & " and [BoatID] = " & Chr(34) & Me![cboSelect].Column(1) & Chr(34)

Notes:
* You can reference any column in a combo box's row source by using the Column(n) syntax. This is a zero-based sequence, so the first column is Column(0), and so forth.

Looking ahead, Access 2000 promises an even more efficient way of selecting a record based on multiple criteria, using hierarchical datasheets that work something like a TreeView control, but are bound to data in Access tables.

OSU | CoF | Community | Search | Forestry Computing Helpdesk

You may direct comments to Websupport
Disclaimer

Last Updated Tuesday, 15-Sep-1998 09:20:21 PDT