Results 1 to 7 of 7
  1. #1
    carletonw
    Guest

    Drill down with 2 Combo's

    Using Access 2000 I developed a application that uses a sub form which contains a combo box control that currently selects all the students in the DB. The parent form has a combo box that selects schools. I want to select a school in the parent and drill down to students that belong to the school. The Schools table has a 1 to many relationship with the students table. Referential integrety is set. Help, I've been stuck for a week.

    Thanks in advance...

    Carleton

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drill down with 2 Combo's

    Open the combo box on the sub form in design mode. Start the query builder, add the two linked tables,

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drill down with 2 Combo's

    Hi carleton,

    The parent form has a combo box that selects schools. I want to select a school in the parent and drill down to students that belong to the school.

    OK, cool.
    You already have the two combo boxes. I suppose for now that you understand how the RowSource property of the combobox works, including the Boundcolumn etc.

    Because the value in the cboSchool changes when you make a choice, you need to change the SQL statement in the cboStudent.Rowsource property because it needs that school value incorporated in its SQL statement.

    The best place to do this is in the cboSchool_AfterUpdate event, like so:

    Private Sub cboSchool_AfterUpdate()
    Dim strCriteria As String
    Dim strSQL As String

    On Error GoTo cboSchool_Err
    DoCmd.Hourglass True

    'store the value from combo bound column
    strCriteria = Me.cboSchool

    'build the SQL string
    strSQL = "SELECT fldStudent FROM tblStudents " & _
    "WHERE fldSchoolID='" & strCriteria & "';"

    'load the existing students
    With Me.cboStudents
    .RowSource = strSQL
    .Requery
    .Enabled = True
    .SetFocus
    End With

    'do other things here like enabling/disabling cmdButtons
    Cleanup:
    DoCmd.Hourglass False
    Exit Sub
    cboSchool_Err:
    HandleErrorHere 'public routine
    Resume Cleanup
    End Sub

    Try it. See if it works for you.
    Don't forget to insert the correct object names etc. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    carletonw
    Guest

    Re: Drill down with 2 Combo's

    KenM

    Thanks for the quick reply. Tried the following code per your instructions:

    Dim strCriteria As String
    Dim strSQL As String

    On Error GoTo SchoolID_Err
    DoCmd.Hourglass True

    'Store the value from combo bound column
    strCriteria = Me.SchoolID

    'Build the SQL string
    strSQL = "SELECT StudentLast FROM Client_Information " & _
    "WHERE SchoolID='" & strCriteria & "';"

    'Load the existing students
    With Form_EncountersSub.ClientInfoID
    .RowSource = strSQL
    .Requery
    .Enabled = True
    .SetFocus
    End With

    Still get no records. Is there something special I do with the Rowsource property of the Sub-Form? Or the Bound Column? I think I'm close.

    Thanks,
    Carleton

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drill down with 2 Combo's

    Hi carleton,

    It doesn't work. OK, that's fair enough.
    Since I can't tell whether you have all the object names correct, I need you to do the following:

    Set a breakpoint at the beginning of this cboSchoolID_AfterUpdate event code block.

    When you make a choice in in school combobox, you will get transferred to the code. Press F8 to step through the code one line at a time. (Forgive me if you already know how to do this).
    Each time a line has been executed, check the values of the variables/controls of that line for their contents. You can just move the mouse pointer over it.

    It is important to make sure that the strSQL statement is correct. You can test it by building the SQL string in the cboClientInfoID, then cutting and pasting it to your code block and finally modifiying so it will include the strCriteria value.

    But each step in the code block must be carefully analyzed and corrected until you get it right.

  6. #6
    carletonw
    Guest

    Re: Drill down with 2 Combo's

    Ken,

    Thanks, I got it to work last night after I emailed you....

    Carleton

  7. #7
    carletonw
    Guest

    Re: Drill down with 2 Combo's

    Rupert,

    Thanks buddy you're solution works good as well. The key here is to use an unbound form in he subform and use a Requery statement in the AfterUpdate event of the first combobox on the main form. Then it works perfectly.

    Carleton <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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