Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 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.

2. 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.

3. 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.

4. 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.

5. 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 columnsArtistID,
   ArtistName, and MusicTypeand is sorted by ArtistName.

6. Create the parameter that links this query to the first combo box.
   For qryFilteredArtists, enter the following in the MusicType field:

   Forms![frmAlbumBrowse]![cboMusicType]

7. 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

8. 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.

9. 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

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, cboMusicTypefor example,
Alternative Rockthe 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.