Linking 2 combo boxes

  • Thread starter Thread starter Lisa Mullin
  • Start date Start date
L

Lisa Mullin

Here is my problem:

I have 1 combo box working with a list of facilities.

These facilities have different departments, so I want to
find a facility on the 1st combo box, and then have the
2nd combo box limited its list to the departments that are
available for the facility being displayed the the 1st
combo box.

How do I link the facility code of the 1st and 2nd combo
box?

Thanks,

Lisa
 
On my website is a small sample database called "CascadingComboboxes" that
illustrates how this is done.
 
Hi Lisa,

If you go to the Row Source for the 2nd Combo Box.
In the query or SQL Statement make sure that the department field is in the
query.
For the criteria for department you will need a full reference to the 1st
combo box.
e.g. [Forms]![frmMyForm]![cmbFacilities]

Then add code such as

Sub cmbDepartment_AfterUpdate
Me.cmbFacilities.Requery
End Sub

Cheers,
Peter
 
That works, the combo boxes are working fine now, but I
get a leftover value in the department when I switch from
one facility to another, how can I have the 2nd combo box
either reset to blank or go to the 1st department when the
facility on combo box 1 changes?

Thanks!!

Lisa
-----Original Message-----
Hi Lisa,

If you go to the Row Source for the 2nd Combo Box.
In the query or SQL Statement make sure that the department field is in the
query.
For the criteria for department you will need a full reference to the 1st
combo box.
e.g. [Forms]![frmMyForm]![cmbFacilities]

Then add code such as

Sub cmbDepartment_AfterUpdate
Me.cmbFacilities.Requery
End Sub

Cheers,
Peter


Here is my problem:

I have 1 combo box working with a list of facilities.

These facilities have different departments, so I want to
find a facility on the 1st combo box, and then have the
2nd combo box limited its list to the departments that are
available for the facility being displayed the the 1st
combo box.

How do I link the facility code of the 1st and 2nd combo
box?

Thanks,

Lisa
Click to expand...


.
Click to expand...
 
It's easiest just to set the second combo to Null after updating the first
(to avoid any leftover values).
So

Sub cmbDepartment_AfterUpdate
Me.cmbFacilities.Requery
Me.cmbFacilities = Null
End Sub


That works, the combo boxes are working fine now, but I
get a leftover value in the department when I switch from
one facility to another, how can I have the 2nd combo box
either reset to blank or go to the 1st department when the
facility on combo box 1 changes?

Thanks!!

Lisa
-----Original Message-----
Hi Lisa,

If you go to the Row Source for the 2nd Combo Box.
In the query or SQL Statement make sure that the department field is in the
query.
For the criteria for department you will need a full reference to the 1st
combo box.
e.g. [Forms]![frmMyForm]![cmbFacilities]

Then add code such as

Sub cmbDepartment_AfterUpdate
Me.cmbFacilities.Requery
End Sub

Cheers,
Peter


Here is my problem:

I have 1 combo box working with a list of facilities.

These facilities have different departments, so I want to
find a facility on the 1st combo box, and then have the
2nd combo box limited its list to the departments that are
available for the facility being displayed the the 1st
combo box.

How do I link the facility code of the 1st and 2nd combo
box?

Thanks,

Lisa
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top