Dependent combo boxes

[Jamie89]

I have read a few articles on this site about having one combo box's selections limited based on the selection in another combo box. I've tried several times but the closest I can get is for it to perform like a parameter query - which is not what I want it to do. Anyone know what I'm missing? My table is called tblCategoriesSubcategories with the two fields Categories and Subcatgeories. I want my second combo box (cboSubcategory) to be limited based on the value selected in the first combo box (cboCategory). My form is called frmTest. Thanks!!

 

[jeffshex]

What's wrong with the parameter query?

You can have your first combo box and then do a combobox2.Requery in VBA on the after update event.

Set the rowsource property of the second combo box to be a query where it looks at the value of the first combo box and uses that as it's parameter.

You can use the IIF with an IsNull to make sure your first combo box has anything and if not, then return all values or something.

 

[Jamie89]

Your statement about setting the rowsource property of the second combo box to be a query that looks at the value of the first combo box as its parameter is exactly what I want it to do - I just don't know how. I know how to set up a query on the rowsource but I don't know how to make it look at the other combo box.

 

[jeffshex]

Ok.
When you are done setting up your first combo box (cboCategory), open up the query window for the rowsource of that second combo box (cboSubcategory).

Pop in your fields and for the criteria that the cboSubcategory is supposed to filter on, you can put something like:

[Forms]![frmTest]![Categories]

If you want it to be more advanced, look into the IIF statement and IsNull to do some more checking of the Categories field.

After you have all that done, go to the properties of the cboCategories and set the After_Update event to do something like me.cboSubcategory.Requery
That way it will refresh the combo box of the second one.

Play around with that stuff and see how it works for you.

 

[TheAceMan1]

How are ya Jamie89 . . .

Hope this doesn't confuse the thread, but if your talking a continuous form, you should only do this with [blue]unbound[/blue] comboboxes! ... [purple]Is this the case?[/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Jamie89]

I'm getting no options in my second combo box (cboSubcategory). The first combo box was not set as unbound so I corrected that first. Here's what I have in the rowsource of my second box:

SELECT tblCategoriesSubcategories.SubCategory
FROM tblCategoriesSubcategories
WHERE ((([Forms]![frmTest]![cboCategory])<>False));

What am I missing?

 

[jeffshex]

Just for good measure, what do you get when you remove the <> false statement?

also, be sure you are doing the after_update event on the first combo box to requery the second.

Here's a decent link that got me going:

http://office.microsoft.com/en-us/access/HA011730581033.aspx

 

[Jamie89]

Ok, I am very, very close to getting this to work. My problem now seems to be that my second combo box is holding onto the options from the first thing I selected in the first combo box and is not refreshing the options when I make a different choice in the first combo box. Am I supposed to have some sort of After_Update event on that one too?

 

[jeffshex]

You should have an After_Update event on the Category combo box that says me.cboSubcategory.Requery

Here is the rowsource from my test Category combo box:

SELECT tblStuff.Category FROM tblStuff GROUP BY tblStuff.Category;

Here is the Subcategory rowsource:

SELECT tblStuff.SubCategory FROM tblStuff WHERE (((tblStuff.Category)=[Forms]![Form1]![Category]));

And the only After_Update event I have is on the Category combo box. That fires the code to requery the subcategory combo box each time a change is made in the category combo box.
Sounds like your super close.