Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 1.3 Limit the Items in One Combo Box Based on the Selected Item in Another

1.3.1 Problem

Sometimes in a form-based parameter query it would be nice to limit the values in one combo box based on the value selected in another combo box. For example, if a form has two combo boxes, one for the type of music and the other for artists, when you select the type of music in the first combo box, you'd like the list of artists in the second combo box to be limited to artists of the selected music type. But no matter which type of music you select, you always see all the artists in the second combo box. Is there any way to link the two combo boxes so you can filter the second combo box based on the selected item in the first?

1.3.2 Solution

When you place two combo boxes on a form, Access by default doesn't link them together. But you can link them by basing the second combo box on a parameter query whose criteria point to the value of the first combo box. This solution demonstrates how you can use a parameter query tied to one combo box on a form as the row source for a second combo box to limit the second combo box's drop-down list to items appropriate to the user's selection in the first combo box.

Follow these steps to create linked combo boxes:

Create a form bound to a table or query. Make it a continuous form by setting the DefaultView property of the form to Continuous Forms. This will be used as a subform, like fsubAlbumBrowse in the frmAlbumBrowse example.
Create a second form with two unbound combo boxes. In the frmAlbumBrowse example found in 01-03.MDB, we named the combo boxes cboMusicType and cboArtistID. Drag the subform from the Access Forms object list in the database window onto the main form. We dragged the icon for fsubAlbumBrowse onto frmAlbumBrowse, underneath the combo boxes.
Set the LinkChildFields and LinkMasterFields properties of the subform control to keep the subform in sync with the main form. We entered ArtistID as the LinkChildFields and cboArtistID as the LinkMasterFields.
Create the query that will supply rows for the first combo box. The query that's the source of rows for cboMusicType is a simple one-column query based on tblMusicType and sorted alphabetically by MusicType.
Create the query that will supply rows to the second combo box. The query that provides rows for the cboArtistID combo box, qryFilteredArtists, contains three columns—ArtistID, ArtistName, and MusicType—and is sorted by ArtistName.
Create the parameter that links this query to the first combo box. For qryFilteredArtists, enter the following in the MusicType field:
```
Forms![frmAlbumBrowse]![cboMusicType]
```
Select Query Parameters to declare the data type of the parameter. Use the exact same parameter name you used in the previous step. For qryFilteredArtists, choose Text for the data type. This query is shown in Figure 1-5.

Figure 1-5. The qryFilteredArtists parameter query links the two combo boxes on frmAlbumBrowse

Adjust the properties of the two combo box controls so they now obtain their rows from the queries created in Steps 3 through 6. In the frmAlbumBrowse example, set the properties of the combo boxes as shown in Table 1-2.
When the value selected for the first combo box changes, you need two things to happen:
- Blank out any value in the second combo box to avoid a mismatch.
- Requery the second combo box so that only matching values will show. In the example, we want to see artists of only the selected music type.
You could use a macro to accomplish this, but adding a VBA procedure is just as easy. To make your code run automatically when the value in the first combo box, cboMusicType, changes, use that combo box's AfterUpdate property. Select [Event Procedure] on the properties sheet, and click the "..." button that appears to the right of the property. This brings up the VBA Editor, with the first and last lines of your event procedure already created. Enter an additional two lines of code, so that you end up with this:
```
Private Sub cboMusicType_AfterUpdate( )
    cboArtistID = Null
    cboArtistID.Requery
End Sub
```

Table 1-2. Key properties for the combo boxes on frmAlbumBrowse2

Name

RowSourceType

RowSource

ColumnCount

ColumnWidth

BoundColumn

cboMusicType

Table/Query

qryMusicType

1

<blank>

1

cboArtistID

Table/Query

qryFilteredArtists

2

0 in; 2 in

1

To see a form-based query in which one drop-down combo box depends on the value selected in another, open and run frmAlbumBrowse from 01-03.MDB. This form has been designed to allow you to select albums by music type and artist using combo boxes, with the selected records displayed in a subform. If you select a type of music using the first combo box, cboMusicType—for example, Alternative Rock—the list of artists in the second combo box, cboArtistID, is filtered to show only Alternative Rock musicians (see Figure 1-6). Once you pick an artist, the form displays all the albums by that artist.

Figure 1-6. The choices in cboArtistID are filtered to show only Alternative Rock artists

1.3.3 Discussion

The parameter query (in this example, qryFilteredArtists) causes the second combo box's values to be dependent on the choice made in the first combo box. This works because the criteria for the MusicType field in qryFilteredArtists point directly to the value of the first combo box.

This works without any macro or VBA code until you change the value in the first combo box. To keep the two combo boxes synchronized, however, you must create an event procedure to force a requery of the second combo box's row source whenever the first combo box's value changes. Any value in the second combo box (cboArtistID) will probably become invalid if the first combo box (cboMusicType) changes, so it is also a good idea to blank out the second combo box when that happens. This is accomplished in the frmAlbumBrowse example by using two simple lines of VBA code placed in the AfterUpdate event procedure of the first combo box.

The subform in this example automatically updates when an artist is selected, because cboArtistID was entered as the LinkMasterFields (the property name is plural because you may need to use more than one field). The LinkMasterFields property can contain the names of one or more controls on the main form or fields in the record source of the main form. If you use more than one field, separate them with semicolons. The LinkChildFields property must contain only field names (not control names) from the record source of the subform.

The example shown here uses two unbound combo boxes and a subform. Your use of this technique for relating combo boxes, however, needn't depend on this specific style of form. You can also use this technique with bound combo boxes located in the detail section of a form. For example, you might use the frmSurvey form (also found in the 01-03.MDB database) to record critiques of albums. It contains two linked combo boxes in the detail section: cboArtistID and cboAlbumID. When you select an artist using the first combo box, the second combo box is filtered to display only albums for that artist.

To create a form similar to frmSurvey, follow the steps described in this solution, placing the combo boxes in the detail section of the form instead of the header. Create an event procedure in the AfterUpdate event of the first combo box, cboArtistID, to blank out and requery the second combo box, cboAlbumID. Because the artist may be different on different records in the form, cboAlbumID also needs to be requeried as you navigate from record to record. You can accomplish this by requerying cboAlbumID in the Current event of the form:

Private Sub Form_Current( )
    cboAlbumID.Requery
End Sub

Using related combo boxes in the detail section of a continuous form can cause problems. Unbound combo boxes will show the same value on every row, and bound ones may mysteriously turn blank when they lose focus. This happens if a dependent combo box has a displayed column that isn't also its bound column. You can demonstrate this by changing the DefaultView property of frmSurvey from Single Form to Continuous Forms. You'll find that cboAlbumID appears blank on all rows that have a different artist than the one selected on the current row. That's because the bound column in cboAlbumID is not the displayed column (the bound AlbumID column has a column width of 0). Access can't display a value that's not in the current row source unless it's in the bound column.

1.3.4 See Also

To fill a combo box programmatically, see Recipe 7.5 in Chapter 7. To optimize your combo box performance, see Recipe 8.3 in Chapter 8.

[ Team LiB ]