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]. Ive tried so many things, Im 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.
Quick explanation: Select Dept. from list of available (or user added Dept.); select Practice Groups available from Dept. associated list (or user added Practice Group)
The code you supplied is already contained within the attached sample. The problem is not a refresh problem, but rather a problem of populating the combobox with a specific list of options dependent upon the contents of the Department control and then displaying the selected Practice Groups name.
I chose to use a subform because there may be muliptle Practice Groups selected. Is there a better way to do this?