Access Cookbook - Ken Getz [13]
When Access runs a query, it checks to see if there are any parameters it needs to resolve. It first attempts to obtain the value from the underlying tables. If it doesn't find it there, it looks for any other reference it can use, such as a form reference. Finally, if there is no form reference (or if you created a form-based parameter and the form is not open), Access prompts the user for the parameter. This means that you must open the form prior to running any parameter queries that contain references to forms.
TIP
Parameter dialogs can sometimes be a symptom of an error in the design of one or more objects in your database. If you ever run a query, form, or report and are prompted for a parameter when you shouldn't be, you probably misspelled the name of a field or renamed a field in a table without changing the reference in the query. Access sometimes creates queries on its own to support subforms or sorting and grouping in reports. You may need to check the LinkChildFields or LinkMasterFields properties of a subform or the Sorting and Grouping dialog of a report to find the unrecognized term that is triggering the errant Enter Parameter Value dialog. Also, if you change a parameter in the query grid, remember to change it in the Parameters dialog too!
1.3. Limit the Items in One Combo Box Based on the Selected Item in Another
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?
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