Edit Cascading Combo Box

[drjohn61]

Hi,

I have a series of cascading combo boxes that work fine. The previous combo box (CB1) sets the row source of the next one (CB2) based on CB1's value. All fine and dandy for all 4 CBs.

My problem is, if someone comes back later and wants to the edit the record, they may just on CB3 (for example) and now it shows all possible selections (not just those based on CB2's value). I have set an 'on change' event to clear any subsequent combos, but can't figure out how to KEEP a combo restricted to a value. I have tried various requeries with 'on click' or 'on dirty' but have not found the right mix yet.

Any help with this one would be greatly appreciated!

John

 

[boblarson]

The issue is - don't set the rowsource via code. Use a query for each combo and limit the appropriate field to the previous combo's value.

so for example you just set combo 3's row source to something like this:

Select * From SomeTable Where [FieldName] = " & [Forms]![FormNameHere]![Combo2NameHere]

and then in the after update event of each combo you just requery them.

 

[boblarson]

Here's a sample using the method I described:
http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

 

[drjohn61]

Great! Thanks! I will give a try and let you know how it works,

 

[drjohn61]

OK,

Something is not working quite right. More background:

tha DB is actually fairly simple, a journal article DB. Each articla has an ID

The form with the CBs is a subform on a tab linked to the specific article ID. The combos populate categories, subcategories, etc. Each article can have multiple cats, subcats, etc...

When I try your method (either referencing the subform or just using me) it asks for the value when the form is opened up.

 

[boblarson]

First, we'll try adding an OR IS NULL after the form reference in the row sources.

So

[Forms]![FormName]![ControlName] OR IS NULL

and see if that helps. If not, perhaps you can upload a copy of your database and we can see if we can fix it.

 

[drjohn61]

OK, Bob.

That still did not work. I am attaching the DB. If you go to Article Info Form and then to the second tab (Details), you will see the CBs I am talking about.

The Db is still fairly early development, so don't laugh TOO hard!

Thanks for taking the time to do this,
John