• Choose a category
• All
• Classic-Fiction

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

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

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