|[ Team LiB ]|
Recipe 1.3 Limit the Items in One Combo Box Based on the Selected Item in Another
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?
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:
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.
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
1.3.4 See Also
|[ Team LiB ]|