Cascadeing Combo Boxes (1 Viewer)

[jason2885]

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.

 

[ajetrumpet]

Probably have to make two forms for that....

Is this the concept you presented before in this thread??
http://www.access-programmers.co.uk/forums/showthread.php?t=132068

Check these out too....

http://www.mvps.org/access/forms/frm0028.htm
http://accessprogrammer.blogspot.co...on-another.html
http://www.techonthenet.com/access/comboboxes/index.php
http://support.microsoft.com/default...b;en-us;209576

 

[jason2885]

Ok I have two combo boxes working right now where if I select a value in one and then another in the other it will show me the correct results but sometimes a I may only want to choose what is in the first box and then filter on that. How do you go about getting it to work like that. I would like this to work much like a pivot table but by just using drop down boxes.

Here is maybe a better explanation of what I need.

I need to be able to have 4 or 5 drop down boxes and depending on what is selected it will display the results. For instance 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.

 

[ajetrumpet]

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

 

[jason2885]

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

ok this may sound stupid but do I need to put that in the criteria parts in the query that this is pulling from or where.

Field 1 refers the field name in the query right.

 

[neileg]

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?

 

[jason2885]

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?

That does get me going 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.

Thanks for the help though.

 

[ajetrumpet]

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.

 

[jason2885]

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. Im not very good with access and I was confused as to where to put the code at.

 

[ajetrumpet]

Where did i need to put that code at.

Whatever object or process you want to have happen when you use the combo boxes for the search (command button, open report, etc...), attach that object or process to an underlying query. You can do this through a "create object" wizard or in the object's properties. In the query's SQL view is where you put the code. That way, when you search, you run the query that is attached to each of your boxes....

 

[jason2885]

I am still not sure what you are talking about. Is there anyway to accomplish this by using criteria and such.

The way that the other person told me I was able to get it to filter the results as it should except when one of the choices in the second combo box and none in the first it returned all of the records where it should have just returned the choice that was chosen in the second combo box.

 

[jason2885]

Here is the where statement I have in SQL view. Can anyone tell me why it is doing the behavior that I described above.

WHERE (((Field2) Like [Forms]![UnitTracking]![Combo4] & "*") AND ((Field1) Like [Forms]![UnitTracking]![Combo0] & "*")) OR ((([Forms]![UnitTracking]![Combo4]) Is Null And ([Forms]![UnitTracking]![Combo4]) Is Null) AND (([Forms]![UnitTracking]![Combo0]) Is Null)) OR ((([Forms]![UnitTracking]![Combo0]) Is Null)) OR ((([Forms]![UnitTracking]![Combo4]) Is Null) AND (([Forms]![UnitTracking]![Combo0]) Is Null));

 

[ajetrumpet]

Jason,

Is it possible to post your database?? I can probably offer a better solution, or maybe even fix it if I can get a look at it...

(The SQL above is a bit jumbled, that probably won't accomplish the task). But I cannot pick it apart enough to determine what purpose it needs to serve....

and Yes, criteria may be better, but again I would have to look to see what you already have done...

 

[jason2885]

Sorry I cant post the database because it has sensitive information in it. basically what that is is the criteria that the other person above suggested for me to use and while it does work like I said it does not let you change the second combo box and show just that one it shows all of the records.

If you have any ideas I would be greatful.

The way that the person above had the criteria written is the way I have it now.


When I try to put your code in the SQL view it gives me an operator error.

 

[ajetrumpet]

Jason, my code above has "!" characters that should have been omitted.
I don't think these are referred to as operators by Access, but they shouldn't be in the code, sorry. If you put that code in SQL view as is, you should be receiving an error, but an OP error doesn't make sense to me. See what happens if you delete the "!" characters that are in red.

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));