(Solved) – I try to make a cascade of combo boxes (4 combo boxes) to filter the information and see only the appropriate result

  • by
(solved)-–-i-try-to-make-a-cascade-of-combo-boxes-(4-combo-boxes)-to-filter-the-information-and-see-only-the-appropriate-result

I try to make a cascade of combo boxes (4 combo boxes) to filter the information and see only the appropriate result. I practically don’t know from this point how to declare and how to use the information I have, I have some code but I have no results… all of the combo are empty.

I find solution on how to add the code separately in the combo but I try to have a dynamic filtering as the criteria is change the results.

Private Sub Ctl1_Click()

    Dim db As Database
    Dim rec As Recordset
    Dim AreaFromV As String
    Dim AreaToV As String
    Dim PolV As String
    Dim PodV As String
    Dim strSQL As String
    Dim strSQLc1 As String
    Dim strSQLc2 As String
    Dim strSQLc3 As String
    Dim strSQLc4 As String



    strSQLc1 = "SELECT DISTINCT Seafreights.[Area From]"
    strSQLc1 = strSQLc1 & "FROM Seafreights"
    strSQLc1 = strSQLc1 & "ORDER BY Seafreights.[Area From];"

    If IsNull(strSQLc1) Then
        AreaFromV = 0
        Me.Requery
        Me.[1].RowSource = ""
        Me.[1].RowSource = AreaFromV
    Else
        AreaFromV = strSQLc1
        [Forms]![pricelist]![1].RowSource = AreaFromV
        Me.Requery
        Me.[1].RowSource = ""
        Me.[1].RowSource = AreaFromV
    End If



    'AreaFromV = IIf([Forms]![pricelist]![1] Is Null, 0, [Forms]![pricelist]![1])

    strSQLc2 = "SELECT DISTINCT Seafreights.[Area To], Seafreights.[Area From]"
    strSQLc2 = strSQLc2 & "FROM Seafreights"
    strSQLc2 = strSQLc2 & "WHERE(((Seafreights.[Area From]) = AreaFromV))"
    strSQLc2 = strSQLc2 & "ORDER BY Seafreights.[Area To];"

    If IsNull(strSQLc2) Then
        AreaToV = 0
        Me.Requery
    Else
        AreaToV = strSQLc2
        [Forms]![pricelist]![2].RowSource = AreaToV
        Me.Requery
    End If

    'AreaToV = IIf([Forms]![SeaFreight]![2] Is Null, [Forms]![SeaFreight]![2], 0)

    strSQLc3 = "SELECT DISTINCT Seafreights.[Port of Loading], Seafreights.[Area From]"
    strSQLc3 = strSQLc3 & "FROM Seafreights"
    strSQLc3 = strSQLc3 & "WHERE(((Seafreights.[Area From]) = AreaToV))"
    strSQLc3 = strSQLc3 & "ORDER BY Seafreights.[Port of Loading];"

    If IsNull(strSQLc3) Then
        PolV = 0
        Me.Requery
    Else
        PolV = strSQLc3
        [Forms]![pricelist]![3].RowSource = PolV
        Me.Requery
    End If


    'PolV = IIf([Forms]![SeaFreight]![3] Is Null, [Forms]![SeaFreight]![3], 0)

    strSQLc4 = "SELECT DISTINCT Seafreights.[Port of Discharge], Seafreights.[Area To] "
    strSQLc4 = strSQLc4 & "FROM Seafreights"
    strSQLc4 = strSQLc4 & "WHERE(((Seafreights.[Area To]) = AreaToV))"
    strSQLc4 = strSQLc4 & "ORDER BY Seafreights.[Port of Discharge];"

    If IsNull(strSQLc4) Then
        PodV = 0
        Me.Requery
    Else
        PodV = strSQLc4
        [Forms]![pricelist]![4].RowSource = PodV
        Me.Requery
    End If

    'PodV = IIf([Forms]![SeaFreight]![4] Is Null, [Forms]![SeaFreight]![4], 0)

    strSQL = "SELECT Seafreights.[Area From], " & _
            "Seafreights.[Area To]," & _
            "Seafreights.[Port of Loading]," & _
            "Seafreights.[Port of Loading Locodes]," & _
            "Seafreights.[Port of Discharge]," & _
            "Seafreights.[Port of Discharge Locodes], " & _
            "Seafreights.Commodity, " & _
            "Seafreights.Curr, " & _
            "Seafreights.[20'STD], " & _
            "Seafreights.[40'STD/HC], " & _
            "Seafreights.Effective, " & _
            "Seafreights.Expiration, " & _
            "Seafreights.[Not Subject To], " & _
            "Seafreights.[Subject to Contract  (see Sheets Arbitraries   Inlands and Surch], Seafreights.[Subject to Tariff Charges], " & _
            "Seafreights.[GEO from], " & _
            "Seafreights.[GEO To], " & _
            "Seafreights.[Amend- ment #], " & _
            "Seafreights.[Owner Sales Hierarchy], " & _
            "Seafreights.[SVC ID]"

    strSQL = strSQL & "FROM Seafreights "
    strSQL = strSQL & "WHERE (((Seafreights.[Area From])= AreaFromV) " & _
            "AND ((Seafreights.[Area To])= AreaToV) " & _
            "AND ((Seafreights.[Port of Loading])= polv) " & _
            "AND ((Seafreights.[Port of Discharge])= podv)) "
    strSQL = strSQL & "ORDER BY Seafreights.[Area From], Seafreights.[Area To], Seafreights.[Port of Loading], Seafreights.[Port of Discharge];"


End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *