Cascading combo boxes going blank after navigating to another record in subform

[Pbear88]

I've been having trouble with an Injury database I've been working on. I've done a lot of research on cascading combo boxes and I've got them working correctly, when I add a new record. But something strange is happening when I toggle through the records(which are on a subform). When I change a record and then toggle to another record, then go back to the previous record, some of the fields from the combo boxes go blank. I know it has something to do with the relationships and the fact that I'm only storing the ID numbers from the cascading boxes, but I'm looking for some guidance on the best way to be able to display the correct data when the clients have to navigate through the series of injury records for a patient. The data in the table is correct, just not displaying all the time on the subform.
Here is a before picture of the form/subform after succesfully choosing the correct choices in each combo box.


Here is what that same record looks like after I move to the next record, make a change in that record, then moving back.

First I'll show you the relationships and field names of the tables I used for the population of the cascading series of combo boxes.


The record source for the subform is a query that is basically the same as the table that stores the injury information (Injury Table). I used a query (qryInjuryTable)for the record source so that I could easily sort the display of the records by descending date.

I'm thinking now that I need to add the tables that have the actual names of the fields(lst_InjuryDescription, lst_InjurySpecifics1, lst_InjurySpecifics2) I'm wanting to keep displayed to this main query.

I saw something on another post that mentioned placing other fields over the combo boxes and a "Bring to Front" code, but I couldn't follow it.

Any help or direction would be greatly appreciated.

[CJ_London]

you probably need to 'reset' the rowsources in the form current event.

But without knowing how you are managing the combo rowsources at present anything else is just speculation.

provide the code you are using, including the events and can probably give more focused advice

[Bulzie]

Not sure you need the relationships like that on those tables, they are just for the combo box lists right? You can filter each combo box by requerying them once the previous combo box value is set using the AfterUpdate event:

BodyPart - use that table as RowSource in the query design i would make the lengths of BodyPartMainID and BodyPartMain to be 0";2" this will hide the ID but still use it as the value that is saved.
InjuryDescription - use that table as RowSource in the query design, add criteria for Me.BodyPartMainID for that column in the table. This will limit the data to only those with same BodyPartMainID. Do same with the field lengths.
Do same for last 2 combo boxes. In the AfterUpdate of each box, blank out the next combo box and requery all following combo boxes like Me.InjuryDescription.Requery If they enter a null or in a combo box, blank out the subsequent combo boxes under it like Me.InjurySpecifics2 = null

Make sure those combo boxes are bound to the fields in your main table. If you want to save the descriptions and not the IDs, then just show the 1 description field and save that to the main table.

[Pbear88]

Thanks for your replies. Here is the Row Source information for the Injury Description combo box. The other two combo boxes (InjurySpecifics1 and InjurySpecifics2) are set up the same way with their respective tables.


The AfterUpdate events just do a requery to the combo boxes.


Each combo box has 2 columns, with the widths set to 0;1 to hide the first column(the ID number). The bound field is set to 1.
When I tried to change the bound field to the text, description field, the lower tiered combo boxes stopped working correctly, because they were including choices that based on the text in the previous combo box and not the ID number.

Ajax- does this give you enough info for the rowsources and afterupdate events?

As for

[Micron]

Are these combos unbound? I don't think so, but I'm kinda confused. If you requery a bound control, the bound data should still be there since nothing has happened that should have removed it unless -

If it is possible that after editing a parent value you create a condition where a relationship between the parent combo and any children no longer exists, you will definitely notice a loss of combo list values since the cascade no longer works. This shouldn't affect underlying data unless the form is based on a query that is no longer satisfied by the combinations you've created. In that case, the data could still be in the table but not in the form's underlying query - until you move off of that record. Then it is gone.

This may be a case where the form should be opened either for viewing or editing, but not both at the same time because that would allow you to trap for missing values only during an edit rather than requery on every record change. Either that, or don't bind the cascading combos to the records.

[Pbear88]

Micron
Right now I have the combos bound to the query that is running the subform (which is basically the same as the main Injury Table that needs to store the values). I think you are right that since this subform is based on a query, there is no underlying way that it can show the text associated with the ID field. If I click on the combo boxes that "disappeared", the text shows back up. I'm thinking that I need to add the tables that run the combo boxes to the query running the subform. Then somehow take the corresponding text value for the respective ID fields and have it displayed.

[Micron]

there is no underlying way that it can show the text associated with the ID field

I don't think I said that, but then again, I could only speak in general terms since the form/subform design isn't apparent in your posts. It is not uncommon to use an ID type of field as the bound data while displaying some other value, so I don't get what that has to do with the data disappearing from view when it was there before cycling off the record and making a change to another record. Perhaps you do not have the correct Child/Parent fields identified between the subform and main form. Perhaps a form query isn't returning the same results after the change (as previously mentioned). Perhaps...
Given that there's so many things it could be (and several interpretations of what you're implying) that I think the best thing is to post a zipped copy of your db along with explicit instructions on how to replicate the issue, as well as the desired goal.

I also don't get the meaning of

...the main Injury Table that needs to store the values

The main form controls should hold the one side of the relationship and the subform the many side. Both are often expected to be editable, as long as the parent/child fields are not allowed to become unrelated. At best, this would create an error message; at worst, disconnected data.

[CJ_London]

the problem is

combo1 has a rowsource of departments Dept1, Dept2, Dept3
combo1 user selects Dept1
combo2 is requeried to show the offices for dept1 - London, Frankfurt, New York
combo2 - user select London

move to next record (combo2 is not requeried)
combo1 user selects dept2
combo2 is requeried to show the offices for dept2 - Hong Kong, Atlanta, Paris
combo2 - user selects Paris

move back to previous record (combo2 is not requeried)
Combo1 has a value of Dept1 - which will display because it is in the rowsource
Combo2 has a value of London, but the rowsource is still set to dept2 - and London is not in that list, so nothing is displayed

solution is in the form current event is to requery combo2 so the rowsource is now based on Dept1 offices

[Pbear88]

I'm sorry I wasn't clear in what the subform's relationship was with the mainform. The mainform displays the patient's name and the subform shows the injuries of that patient. The parent/child link is with the patient's ID number. So there is a one-many relationship between patients and injuries. In reading about cascading combo boxes, many showed how to perform this on a mainform, but very few showed how to do this in a subform that would have multiple records using the cascading combo boxes for each separate record. The few that did show how to do this in a subform, just said to reference the mainform, then the subfom, then of course, the combo box's name- in the rowsource parameter. I just don't understand why the Injury Description, InjurySpecifics1, and InjurySpecifics2 combo boxes go blank after navigating to another record. And why does the combo box for the MainBodyPart not disappear, when it also is stored as the just the ID in the Injury Table? See the first two pictures from the first post.

Thanks again to everyone who has posted.

[Pbear88]

Thanks Ajax. I'll try that!

[Pbear88]

Ajax, you are fantastic!!!! That worked!!!!!! Thank you so much!!!

[CJ_London]

Happy to help.

The other thing to consider, which I don't think you have, is this scenario.

combo1 has a rowsource of departments Dept1, Dept2, Dept3
combo1 user selects Dept1
combo2 is requeried to show the offices for dept1 - London, Frankfurt, New York
combo2 - user select London

combo1 user realises they have made a mistake and selects dept2
combo2 is requeried to show the offices for dept2 - Hong Kong, Atlanta, Paris
combo2 is now apparently blank but user does not select an office (perhaps they are not sure which one). However it is still populated with London

user moves to next record

You now have a record saying department is dept2 and office is London - which is wrong

Solution - in addition to requerying combo2, the code should also reset the combo2 value to null (or 0 or "", whatever the default value would be for the underlying field)

A more sophisticated solution would be to see if the 'old' value of London is in the new rowsource (perhaps both dept1 and dept2 have an office in London), if it is, leave it, if its not, set to null.

[Micron]

@Ajax
Since I cannot recall ever using cascading boxes on anything other than a single record subform (i.e. without navigation controls) or on the main form only, I'll have to defer to you - especially since you appear to have solved the problem. If anyone had asked me, I would have said that requerying a bound combo would display the underlying data (so that if it had changed, the new data would be shown) and has nothing to do with its row source (list). A bound combo is designed to show the current underlying data in the field it is bound to. Changing its rowsource should have no bearing on this. I can say that as a test, I altered the rowsource query of a combo and navigated back and forth over the record and there was no change to the underlying data, although the list did change. Mind you, the combo was on the main form, not a subform. Not sure I would put combo boxes on a subform.

P.S.: whatever I read today regarding the requery behaviour of a combo supports my take on how it should work. I guess either it doesn't, or there's something else involved that we don't know about. Then again, it's possible that MSDN doesn't know everything.

[Pbear88]

Great suggestion Ajax. I'll be sure to look at that. Thanks again for your help. Thank you to everyone who posted!

[CJ_London]

We might be talking at cross purposes

A bound combo is designed to show the current underlying data in the field it is bound to

I would modify that slightly to add to the end 'where there is a match in the rowsource between the value and the bound column'

I can say that as a test, I altered the rowsource query of a combo and navigated back and forth over the record and there was no change to the underlying data, although the list did change.

I agree, changing the rowsource does not change the underlying data. In your test, when you changed the rowsource, did you change it to something that does not match the underlying data - if so, what do you see I the combo (not the dropdown)?

e.g.

a combo has a pre-existing value of 125
the combo bound column is 1
the rowsource is 'SELECT ID, name FROM myTable'
the column count is 2
the column width is 0cm;2cm;
lets say the name for record with an ID of 125 is Smith
So you should see Smith in the combobox

now modify the rowsource to 'SELECT ID, name FROM myTable WHERE ID<125'
Do you still see Smith in the combobox?

change the rowsource back to 'SELECT ID, name FROM myTable'
You should now see Smith in the combobox again.