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).
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.