Combo Boxes to Filter

[Jillbo]

I have a form where the user can choose from 4 combo boxes to Filter the data appearing on a subform. At the moment only one combo box can be used to filter at a time. How can I get the data filtered by 2 or more combo boxes?
Cheers
Jill

 

[WayneRyan]

Jill,

That's been covered a lot of times here. Use the Search
Facility and look for "Cascading combos".

Wayne

 

[Jillbo]

I looked through 7 pages worth before writing this question. Many of the questions wanted to restrict the actual data in each combo box based on the choice picked from the first combo box. All I want to do is have the data on the subform filtered based on 2 combo boxes.
eg:
Combo1 = tblPosition
Combo2 = tbleCertification

User chooses a position and a certification. The subform then lists all the employees in that position with that certification.
Currently using code on After Update on each combo box.

Excuse my ignorance if this is the same thing. Please advise.
Thanks Jill

 

[R6Flyer]

Jill,

Base the subform on a parameter query which references the 2 combo boxes.

On the afterupdate event of each combo box requery the subform.

That should achieve what you want.

Cheers

Flyer

 

[Jillbo]

Hi Flyer,
How do you reference the combo boxes in the query?
Cheers
Jill

 

[WayneRyan]

Jill,

You can get the query in design view and for the fields in
question enter this as the criteria:

Forms![YourForm]![YourCombo1]

The form must be Open at the time for the reference to be
valid.

Wayne

 

[Jillbo]

Hi Wayne,
Thanks it does work. I appreciate your help.
However...... By setting conditions in the query I have restricted the user.
What I was ideally hoping to do was when the user chooses an item in the combo box to filter the subform they should then be able to filter within those results.
Any suggestions?
Cheers
jill

 

[WayneRyan]

Jill,

If the query references the combo in its criteria, doesn't that
filter?

Or do you have to put "both" combos in the query? Then you
can filter on them both.

Wayne

 

[Jillbo]

Hi Wayne,
By setting conditions within the query the user has to choose both conditions straight away. What I am trying to achieve is a filter within a filter. The user chooses the first filter option, gets the information they require and then narrows down the field by running another filter within the already filtered records.
???????
Cheers
Jill

 

[R6Flyer]

Jill,

When referencing the combo boxes in the query use an expression such as:

Like "*" & Forms![YourForm]![YourCombo1] &"*"

for each of your combos.

This will allow the user to make a selection in the first combo and see the results then make a selection in any subsequent combo box(es) to filter the subform further.

Cheers

Flyer

 

[waipahunet]

Do you guys know how to get this to work in access data project? I have a subform combobox that runs a stored procedure query based on a control in the main form.

I type =Forms![frm_claimsentry]![PrincipalCode] in the criteria section of the query but it automatically converts it to = N'Forms!frm_claimsentry!PrincipalCode'

If I run the query by itself it's supposed to prompt me for [PrincipalCdoe] but it doesn't. If I do the same in an .mdb database it works fine.

Anyone got any suggestions?

Thanks!