combo box acting as filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 1 form called frm_InHouse with 2 combo boxes. There are 2 tables:
clinics and users. tbl_clinics contains clinic, clinic_no and tbl_users
contains clinic_no, clinic, and user. The 2 combo boxes are named
Clinic_Name and User_Name respectively.
When I select a clinic in Clinic_Name (combo box) I'd like a list of users
for that clinic to appear in the 2nd combo box called User_Name.
Is there a simple way to do this?

thanks in advance!!
 
Hi Tag

First, assuming clinic_no uniquely identifies a clinic, then you should
remove the clinic(name) field from tblUsers, as it is redundant.

Second, to answer your question, add a WHERE condition to the RowSource of
your second combo to include only records where clinic_no matches the first
combo:
... WHERE clinic_no=[Forms]![your form name]![cboClinic]

Then, in the AfterUpdate event for cboClinic, requery the second combo:
cboUser.Requery

Note that the bound column of cboClinic should be the Clinic_No, not
Clinic_Name.
 
Back
Top