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

[Micron]

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 would not, because a bound combo can display the underlying data and have no row source at all - not that it would make sense to do so. The 1st & 2nd images are the first and second table records on a single form with nav controls. The third shows the result of the list when the control has no row source:

In your test, when you changed the rowsource, did you change it to something that does not match the underlying data

I'm thinking you're referring to the value selected from the row source list or value list. Changing the selected value does not affect the row source AFAIK.

now modify the rowsource to 'SELECT ID, name FROM myTable WHERE ID<125'

I would not expect to see Smith because its ID is 125 and you're limiting list rows to anything up to 124, but not 125.

change the rowsource back to 'SELECT ID, name FROM myTable'

nor would I expect to see Smith if changing the row source eliminates the possibility. Not understanding why you'd suggest this since row sources are not typically changed for cascading combos. I hope we're on the same page when it comes to row source versus control source.

This is just one of my beer fogged days I guess. I just don't see why cycling from one record to another just doesn't display the proper data that the combos are bound to, regardless of what has happened to the record before or after the one you just navigated back to. If we're talking about a combo losing its row source list because it doesn't jive with one it's dependant upon, I missed it. Maybe I'll play around with this another day and try to replicate the behaviour. For now, time for more beer and baseball.

[CJ_London]

I would not expect to see Smith because its ID is 125 and you're limiting list rows to anything up to 124, but not 125.

That is my point

Not understanding why you'd suggest this since row sources are not typically changed for cascading combos.

Perhaps this is where we differ - I believe the rowsources do change for cascading combo's (except the first one)

OK the OP is using a query as the rowsource, so the 'text' of the rowsource doesn't change, but the underlying sql does which affects what is available in the combo dropdown

[Micron]

OK the OP is using a query as the rowsource, so the 'text' of the rowsource doesn't change, but the underlying sql does

Call me old and disagreeable (amongst other things) but I'm going to disagree with that also. If the 2nd combo's rowsource property is

SELECT tblMyTable2.SomeField FROM tblMyTable2 WHERE tblMyTable2.AnotherField = Forms!frmMain!cmbName

and I select a value from any combo (whether or not that selection is a changed value) the sql statement (rowsource property) is not changed in any combo. I would not even say that the criteria (Forms!frmMain!cmbName) changes. I say this because if you examine the rowsource, it will read exactly the same at any time as long as it's not altered in code. Thus neither the sql or criteria changes. What changes is the value contained in the control (the .Value property value).

I prefer to think of the criteria in this case as being a reference to the object's value property. I maintain that in my example what we're really saying is, "the criteria to use in retrieving rowsource records is the control's .Value property", and the value of that property is the only thing that's really changing. Thus my rowsource sql statement can evaluate to Smith or Jones without changing one letter of the control's rowsource statement. Maybe few others think of it that way, but I maintain that's what's really going on.

Back to baseball (tonight they're losing).

[CJ_London]

Sorry - should have said - what the underlying sql returns as a recordset.

Better luck with the baseball