Full Version: Multiple Cascading Combo Boxes
UtterAccess Forums > Microsoft® Access > Access Forms
Okay, I've been working on this for a while now and now I seem to be hit with a problem to which I cannot find a solution.
I have multiple cascading combo boxes on a subform (main form is continuous) and I used this text box workaround (placing a text box with row source to the same query as other combo boxes over the combo box in question) so the combo box would always show the correct value and not a blank in case of changing one of the combo box on the form.
And it works BUT...
Oneed also to in some cases left these combo boxes empty and now when I have this text box workaround, it seems to be impossible as I get the popup saying: The Microsoft Access database engine cannot find a record in the table 'tblName' with key matching field(s) 'fieldName'.
This is of the text box I suppose but is it possible to have the text box workaround (or maybe something else) and leave these combo boxes empty...? Or is it either the text box workaround or possibility to leave combo box empty (which would be very confusing for the users because of the combo boxes changing to empty in the form).
Some expert comment/help/tips would really be appreciated...
If you have 'cascading combo boxes' then the list for each box is dependent on the value in the previous box.
o if you don't select a value in, say, box 3 then what do you expect to happen in box 4?
That's not the case, I have two cascading combo boxes but in multiple rows and if combo box 1 is empty, then is also number 2.
asically it goes like this I have a table for Parts, Subparts and Sub-subparts. One part can have many subparts and one subpart can be in many parts. Same with subparts and sub-subparts. These are connected through junction tables. Then I have a task table which has tasks for parts or parts and subparts or parts and subparts and sub-subparts. Cascading combo boxes are on the form so that I am able to choose that if I have part1, then combo box 1 will show subparts which are connected to part1 and when I select subpart then I can select from combo box 2 sub-subparts which are connected to subparts.
And with these cascading combo boxes I use text box workaround (placing text box over combo box) on the form so that they won't show empty.
And it works fine if part, subpart and sub-subpart have values but I'm not able to only have part or part and subpart to have values on the form.
SO how do you set the rowsources?
It seems to me that the rowsource of each combo should only be set in the afterupdate event procedure of the 'previous' control.
That also means that in the design, you set a rowsource that produces no output (Select field1 from table where 1 =0) and that the rowsources should be reset in the form current event. If the current record is a new record then agian you would set the rowsources to have no records, but if it's not a new record then you can use the values in each control to set the rowsources (obviously testing for nulls and using the 'no records' SQL if necessary.
I managed to solve this by changing in the table's design this field to allow zero length strings. After that, didn't get any errors.
Thanks for your help!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.