Dependent combo boxes - populating question (1 Viewer)

[aalrr]

New to Access and head spinning. I have created a simplified form to simulate my needs. Main form (frmMain_PG2); record source = tblAssignments. Department combo (not restricted to a list); DeptID as control source. The control displays the Department name from tblDepartments. So far so good. Next, Practice Group subform (sfrmPG2_Test) displays the practice group(s) attorney is a member of. The PracticeGroupID (control source = PGrpID) displays correctly. (The names of the Practice Groups are in tblPracticeGroupLists.)

Now my problem -- Practice Groups are dependent upon DeptID. In tblPracticeGroupLists, a field called DeptAssoc holds the ID for the related department (based on DeptID in tblDepartments). I tried converting PracticeGroupID to a combo box (not limited to list). The columns I set up were PGrpID (tblPracticeGroups) and PracticeGroup (name of practice group from tblPracticeGroupList). The bound column is column 1. Once I threw the Dept. combobox dependency into the ring, I fell into the abyss. In a SQL query, I included DeptAssoc (tblPracticeGroupsList) and set criteria to [Forms]![frmMain_PG2]![DeptID]. I’ve tried so many things, I’m not sure I have the relationships correct at this time.

I also want users to be able to add a new record and have it write to all related tables correctly.

Your help will be greatly appreciated.

 

[jsv2002]

Ok so what do you want to do? do you want to show certain records in your data sheet view depending on selections in main form?

If so something similar I did and example for using combo box and dates is attached but not really sure what your trying to do...

 

[aalrr]

I want all to select ALL active employees. The main form will show OR add one employee at a time. Each employee will be a member of (or be able to select) a specific Department. Certain Departments also have Practice Groups associated with them. The employee may be a member of more than one Practice Group. Based on the employee's selected Department, I want a Practice Group drop down combo to show options for the related Practice Groups as well as display the name of the Practice Group once it is selected.

Employee > Department > Practice Group dependent combo

Employees exist in tblEmployees
Department assignments for each employee exist in tblAssignments
Department names exist in tblDepartments
Practice Group assignments exist in tblPracticeGroups
Practice Group names exist in tblPracticeGroupList

I hope this explains the scenario better. Thanks.