Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Selection order in cascading combo boxes

    Hi
    I have a simpel form that on it has 2 combo boxes. By the user selecting a selection form the first combo (BrkSelect), filters the selection available on the second combo box (policies2). For example the the first combo box has a list of brokers, the second combo then lists the references applicable to that broker.

    I have set the following code against the "After Update" Event on combo1 (BrkSelect)

    Private Sub BrkSelect_AfterUpdate()
    On Error Resume Next
    Me.policies2.Value = ""
    policies2.RowSource = "Select Policies.RiPolicyRef " & _
    "FROM Policies " & _
    "WHERE Policies.BrokerGroup = '" & BrkSelect.Value & "' " & _
    "ORDER BY Policies.BrokerGroup;"
    End Sub

    I have then set the "On Current" event for the form with the following code

    Private Sub Form_Current()
    On Error Resume Next
    BrkSelect = DLookup("[BrokerGroup]", "Policies", "[RIPolicyRef]='" & policies2.Value & "'")
    policies2.RowSource = "Select Policies.RiPolicyRef " & _
    "FROM Policies " & _
    "WHERE Policies.BrokerGroup = '" & BrkSelect.Value & "' " & _
    "ORDER BY Policies.RiPolicyRef;"
    End Sub


    This all seems to work fine except that the selection in combo box 2 is not in the the correct order, it should be in assending order but is random. I thought that by using the ORDER BY for the "RiPolicyRef" (which appears in combo 2) would give me the correct order.

    Can anyone suggest what I am doing wrong.

    Thanks a lot
    John

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I'm not familiar with the way you are using the DLookup. What is the intended purpose of the onCurrent function?

    Perhaps you could trap the error instead of resuming. This will show if there is an error that you are not seeing.

    e.g.
    On error goto ErrorHandler
    .
    .
    your code
    .
    .
    goto EndFunct 'by pass errorhandler
    ErrorHandler:
    msgbox err.number & vbcrlf & err.description
    EndFunt:
    end sub
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    The OnCurrent part is to syncronise the 2 combo boxes to ensure that the selection available matches the first selection

    Regards
    John

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Ok, solved it now. It was the last part of teh OnCurrent event, it was sorting the wrong field, obvious really!!!


    Regards
    John

Posting Permissions

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