Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple linked combos - each dependant on each other.

Combo Boxes

Multiple linked combos - each dependant on each other.

by  Darrylles  Posted    (Edited  )
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
[tt]
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
[/tt]
Hope this is clear enough.

Regards,

Darrylle
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top