I've struggled on numerous occasions in the past to have combos requery (in a 2+ combo structure) dependant on the value of the preceding combo. I have also solved it in the past, but have decided to get it into the FAQ section so that I can find it again!
It's always been difficult to get the message across to Tek-Tip helpers as to what I want exactly.
To get 2 combos synchronising is easy, and thus - helpers assume that adding a third combo will then requery automatically in the same way; not so - the third combo DOES NOT react in the same way, whether it be due to an MS Access 'undocumented feature' or bug. The 3rd combo WILL react in the same way to the 2nd combo change, but will not react in the same way when the 1st combo is the instigator of the 2nd combo change.
i.e. You can have the 2nd combo react to a change from the first - no prob. You apply the same code, in the same method for the 3rd combo - it DOES NOT respond in the same way.
My solution achieves this: When combo1 is changed, combo2 changes it's contents and displays the first item. Combo3 then changes IT'S contents and displays IT'S first item - because the 2nd combo has changed.
Table structure/relationship: The combo's are based on a table structure of 1 -> Many -> Many. Topic -> Area -> Course. Table structures have been simplified, but makes no difference to example. Combo's contain pk and name columns. PK being the primary key, fk being links to related table. Combo's are populated via query which selects dependant on it's fk being equal to the related table's pk.
e.g: SELECT tblArea FROM tblArea WHERE topic_fk = cmbTopic
tblTopic tblArea tblCourse pk pk pk name name name topic_fk area_fk
There are many tblTopic records, many tblArea records in a Topic, and many tblCourse records in an Area.
The code: When Topic is changed, this changes possible values for Area, and then possible values dependant on Area for Courses...
Private Sub cmbTopic_Change() cmbArea.SetFocus cmbArea.Value = Null cmbArea.Requery cmbArea.Value = cmbArea.ItemData(0) cmbCourse.SetFocus cmbCourse.Value = Null cmbCourse.Requery cmbCourse.Value = cmbCourse.ItemData(0) cmbTopic.SetFocus End Sub
'When Area is changed - changes possible Course values... Private Sub cmbArea_Change() cmbCourse.SetFocus cmbCourse.Value = Null cmbCourse.Requery cmbCourse.Value = cmbCourse.ItemData(0) cmbArea.SetFocus End Sub