Combo Box

[Guest]

Hi

I'm hoping someone can help.

What i am trying to achieve is when i select a value in the first combo box
that the values in the second combo box dispalys only those values related to
the selection in the first combo box.

For example i may have the following; DEPARTMENT- finance, HR, sales and
customer services.

the finance department may have the following sub departments; cashiers,
banking and reconciliations.

What i'd like is that when i choose the DEPARTMENT Finance in the first
combo box that the second combo box (SUB DEPARTMENT) only displays; cashiers,
banking and reconciliations.

I believe that the query SELECT DISTINCT maybe part of the answer but I
imagine it depends on how your tables are set up.

Any help would be greatly appreciated.

Thanks

 

[Guest]

Step 1:
Create 2 tables: tblDepartmentID and tblSubDepartmentID. Insert DepartmentID
field in tblDepartmentID and set is as Primary Key. Insert 2 fields
DepartmentID and SubDepartment in tblSubDepartmentID.

Step 2:
Create one to many relationship: tblDepartmentID to tblSubDepartmentID.

Step3:
Create a form (name it Form3) and insert two combo boxes: cboDepartmentID
and cboSubDepartmentID. Insert the following codes in Row Source of each
combo boxes:
cboDepartmenID
SELECT tblDepartment.DepartmentID FROM tblDepartment;
cboSubDepartmentID
SELECT tblSubDepartment.DepartmentID, tblSubDepartment.[Sub-Department] FROM
tblSubDepartment WHERE
(((tblSubDepartment.DepartmentID)=Forms!Form3!cboDepartmentID));

Ensure both the combo boxes' Row Source Type is "Table/Query".

Step 4:
Insert the following code in the After Update event of cboDepartmentID:

Private Sub cboDepartmentID_AfterUpdate()
Me!cboSubDepartmentID.Requery
End Sub

Step 5:
Save and run the form.