Access - using multiple combo boxes to select records

Question

I have a table with employee names and department and division codes.  I create two combo boxes to select the department value and then the division value.  That works fine. I want to see a subform that only has employees from that combination of department and divisions.  For some reason, even though I can run a query linking to the combo forms, when I try to add the subform to the combo selection form, I cannot get it to work.  I'm obviously missing something basic.  Any help appreciated.

Answer 1

Why use a subform?  You can simply put the combo boxes in the header of a bound form whose RecordSource is a query which references the two combo boxes as parameters.

For an example of this take a look at ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option to "Open simplified form for 'drilling down' through hierarchies to return records".  In the form three unbound combo boxes in the form header are correlated by, in the case of the District and Parish combo boxes, referencing the combo box above as a parameter.  In the AfterUpdate event procedure of each combo box the combo boxes in the hierarchy are set to Null and requeried, and the form is requeried to progressively show the matching rows.

Answer 2

Your subform should be bound to a query that takes as its criteria, the Dept and Div selected in the those comboboxes using the syntax:

Forms!formname!controlname

Once the selection are mad you requery the subform and it should display employees that meet the criteria.