Forms / cascading comboboxes blank-out?????

[KingKong]

Hi there,

Probably a simple question, but it's got me puzzeled for a few days now...
(I've been 'roaming' the forum for 2 days, hoping to find an answer to my problem. My persues where fruitless, however).

Here's my problem:

I've got a form that's bound to a table, on the form there are 2 'single' comboboxes and 2 'cascading' (that's 2 sets of 3).
When I fill out the form and move on to the next record, the previous record is, at that point, still showing the previously selected values in the cascading comboboxes. When I close the form en re-open it again, all cascading comboboxes (this goes for every record that I've entered so far on the form) 'blank-out'. When I look a the table however, the entered data is still there (as it should be).

I'm fairly new at Access and probably my explanation of the problem needs some work (elaboration) en perhaps my English (because it's not my native language) is not correct at all times...
Still I hope someone will adress my problem!!

Greetings,

Peter.

 

[Pat Hartman]

Even if English were your native language, this explaination would still be hard to understand so post back if you don't understand.

The value displayed in the combobox is, as you know, not the value that is stored in the form's recordsource. The value that is stored is the field referenced in the control's controlSource. That value is matched against the recordset referenced by the combo's RowSource. The value that is displayed, is what is contained in the first visible column of the RowSource. If no value matches, the field will appear empty even though there is actually a value in the controlSource.

For example, you have a list of contacts and each contact has a ContactID, ContactName, and ActiveFlag. Your RowSource query selects all contacts whose ActiveFlag is True.
Sample Data:
1, Mary, True
2, Sam, False
3, John, True

So the RowSource query would return:
1, Mary, True
3, John, True

But, if the controlSource, which is the field bound to the table, contains 2 because Sam was the contact selected when the record was made, then the combo will "appear" empty because there is no record for ContactID = 2 in its RowSource.

This is essentially what happens in a continuous form when you have cascading combos. The RowSource for each row might be different but since from Access' perspective, there is only ONE combo control, even though several rows may be visible in the subform, Access can only keep in memory one RowSource recordset at a time.

You can reduce the problem by requerying ALL your combos in the Current event of the subform. That way at least, the record you are working on will show the correct rowSources.

 

[KingKong]

!

Pat,

Think I understand what you are saying here!
I've mixed up what's actually stored (RecordSource) and what's being displayed (first visible column of the RowSource). After your elaborate explanation I also understand the concept of only one RowSource recordset being 'active' (I've seen it happen on my form!).

I've tried your suggestion to put some code behind the form's OnCurrent event to requery my cbo's, that works fine for the record being worked on (as you've explained).

I'll probably have to do some re-design to be able to actually see what's entered at all times (without having to take a 'peek' at tables ). I have some ideas already.

Thanks for putting me on track again!

Kind regards,

Peter.