Combo Box Fill List 1

[ITProg]

I would like to fill a combo box list based on the value in the combo box above it on the same form. The first combo box lists staff departments. In the second combo box I would like to list staff names based on the department selected in the first combo box. The following query is from the row source in the second combo box. It only lists staff names from one department and does not change when I choose a different department.

SELECT [tblStaff].[LNAME] & ", " & [tblStaff].[FNAME] AS staff, [tblStaff].[DISCIPLINE] FROM tblStaff WHERE ((([tblStaff].[DISCIPLINE])=IIf(Trim([Forms]![frmReviews]![cmbOTHERName])="Recreational Therapy","RT",IIf(Trim([Forms]![frmReviews]![cmbOTHERName])="Occupational Therapy","OT","Physical Therapy")))) ORDER BY [tblStaff].[LNAME] & ", " & [tblStaff].[FNAME];

 

[M8KWR]

have you requeried the 2nd combo box using the afterupdate procedure in the 1st combo box.

if you let me know the names of the 2 combo boxes, then i can write the code for you...

 

[ITProg]

The name of the first combo box is: [Forms]![frmReviews]![cmbOTHERName]. The name of the second combo box is: ([Forms]![frmReviews]![cmbOTHERBy]. The names to fill the second combo box are in table tblstaff.

 

[PHV]

In the AfterUpdate event procedure of cmbOTHERName add the following code:
Me!cmbOTHERBy.ReQuery
Just a note: you may simplify the cmbOTHERBy.RowSource like this:
SELECT LNAME & ", " & FNAME AS staff, DISCIPLINE FROM tblStaff WHERE DISCIPLINE=IIf(Trim(Me!cmbOTHERName)="Recreational Therapy","RT",IIf(Trim(Me!cmbOTHERName)="Occupational Therapy","OT","Physical Therapy")) ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244