Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Question Unanswered: Dependent Combo Box

    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.

    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)

    Your help will be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's one solution:

    Code:
    Private Sub DeptID_AfterUpdate()
        ' DBEngine.Idle dbRefreshCache
        ' DoEvents
        ' DoEvents
        'Forms!frmMain_PG2!sfrmPG2_Test.Form.Requery
        Me.sfrmPG2_Test.Form!PracticeGroupID.Requery
    
    End Sub
    What I do not understant though is why you put the PracticeGroupID combo on a subform.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    5

    Not a refresh problem

    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?

    Still puzzled.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •