I need to have about 4 or 5 combo boxes in the same form that will when I make a selection from one it will filter itself until I am left with just what I need in a subform. I also need it to be able to select any one of the boxes anytime.
In that case, you need to use "AND"s and "OR"s...I may only have one criteria selected but I need for all of the records that fall in that criteria to show but there are times when I might used 3 or 4 or even all 5 of the criteria to display the records.
In that case, you need to use "AND"s and "OR"s...
SELECT ()
FROM ()
WHERE ((Field1=Forms![form name]![Field1 control name] OR Forms![form name]![Field1 control name]! IS NULL) AND
(Field2=Forms![form name]![Field2 control name] OR Forms![form name]![Field2 control name]! IS NULL) AND
(Field3=Forms![form name]![Field3 control name] OR Forms![form name]![Field3 control name]! IS NULL) AND
(Field4=Forms![form name]![Field4 control name] OR Forms![form name]![Field4 control name]! IS NULL) AND
(Field5=Forms![form name]![Field5 control name] OR Forms![form name]![Field 5 control name]! IS NULL));
Jason,
The term "cascading" when discussing combo boxes means "filtering"....for future reference...
I think what you want is the ability to select some or all of the criteria and still return results when some criteria are not selected?
You can do this using the LIKE operator and the wildcard *.
So the criteria in your query end up looking like this:
LIKE Forms!MyForm!MyCombo & "*"
Where the combo is not selected, this formula returns
LIKE *
which has the effect of returning all records.
Does that get you heading in the right direction?
The only problem Im having now is that when I leave the first one combo blank and try to select one in the second combo box it shows all of the records just not the ones that it should.
Jason, the code I wrote above should fix this problem, did you not try it??
Maybe I misunderstood the whole thing, I'll stay out of the way.
Where did i need to put that code at.
SELECT ()
FROM ()
WHERE ((Field1=Forms![form name]![Field1 control name] OR Forms![form name]![Field1 control name][COLOR=red]![/COLOR] IS NULL) AND
(Field2=Forms![form name]![Field2 control name] OR Forms![form name]![Field2 control name][COLOR=red]![/COLOR] IS NULL) AND
(Field3=Forms![form name]![Field3 control name] OR Forms![form name]![Field3 control name][COLOR=red]![/COLOR] IS NULL) AND
(Field4=Forms![form name]![Field4 control name] OR Forms![form name]![Field4 control name][COLOR=red]![/COLOR] IS NULL) AND
(Field5=Forms![form name]![Field5 control name] OR Forms![form name]![Field 5 control name][COLOR=red]![/COLOR] IS NULL));