Cascading Combo Boxes -- 3 Levels -- 3rd Level Not Updating Properly

[thadius856]

I'm sure this comes up often, but I've searched and not found the exact fix. I'm 99% of the way, I think. All three boxes work, except one use case: if I drill down to the 3rd level, then change the 1st level, the 2nd level updates but the 3rd level does not. I'm guessing it's because, by design, my 3rd level is N/A except for a small handful of items.

My previous attempts had some weird table structures, so I went ahead and standardized to the oft-recommend 3-table system with each table linked to the previous level, and all 3 back to the main table which the form is bound to. I could describe it in length, but a picture says a thousand words:



1st level - rowsource is query of 1st level table. AfterUpdates Requery added for 2nd and 3rd level cbo's.
2nd level - rowsource is query of 2nd level table with cbo criteria for the 1st level FK. AfterUpdate Requery added for 3rd level cbo.


3rd level - rowsource is query of 3rd level table with cbo criteria for the 2nd level FK. .

Question: am I missing some more Requery's? Should the 3rd level have Requery's for the other two? Should all three levels be Requery'ing each other? I can't seem to find a combination that works, and I'm suspecting it's because most of my item types don't have a 3rd level assigned.

[ItsMe]

Often times, what I do, is adjust the .Visible or the .Enable property of the dependent ComboBoxes based on values of parent Combo's

[June7]

The only value really need to save into tblMedia is MediaFormFK. Related info from tblMediaTypes and tblMediaTechs can be retrieved in queries that join tables.

Dependent comboboxes should NOT requery the preceding comboboxes - makes no sense because preceding comboboxes do not have filter criteria referencing succeeding comboboxes.

Might want code in preceding comboboxes that set the dependents to Null.

[ssanfu]

Question: am I missing some more Requery's?

Possibly/probably. You haven't posted the code.

Assuming three cascading combo boxes: cboMediaTech -> cboMediaType -> cboMediaForm

Looking at the picture:
If you change cboMediaTech, then cboMediaType & cboMediaForm should be set to NULL and both requeried.

If you change cboMediaType, then cboMediaForm should be set to NULL and requeried.



I have a demo dB with 5 cascading combo boxes if you want to tear it apart to see what I did.....

[thadius856]

Assuming three cascading combo boxes: cboMediaTech -> cboMediaType -> cboMediaForm

Looking at the picture:
If you change cboMediaTech, then cboMediaType & cboMediaForm should be set to NULL and both requeried.

If you change cboMediaType, then cboMediaForm should be set to NULL and requeried.

Your assumption is correct. Yes, both of those things were happening if I went left to right.

But then say I decide I don't like the first box (cboMediaTech) after having picked all three. The 2nd (cboMediaType) was set to NULL and requeried, but the 3rd (cboMediaForm) was not.

[thadius856]

I sat down and had a long hard think about what I'm trying to accomplish here.

With this dataset, I think I may have been trying to force data into those three tiers when it wasn't particularly necessary or even helpful... shoehorning the data into three tiers when it wasn't appropriate.

I axed the third tier -- along with its relationships, tables, comboboxes and queries -- and the data still fits nicely into those two tiers without much modification at all.