INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combo Boxes

Multiple linked combos - each dependant on each other. by Darrylles
Posted: 28 Dec 03

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

Hope this is clear enough.

Regards,

Darrylle

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close