QBF with 2 combo boxes as controls (1 Viewer)

J

JPRENG

Registered User.
Local time
Today, 07:39
Joined
Jun 26, 2013
Messages
37
What I am trying to do is, for example, I have 2 combo boxes, you can select a value in the first one and search and your results match the criteria, or you can select a value in the first AND second one and the records dispalyed will match the first AND second combo box criteria. Any Ideas?
 
B

Bandylank

New member
Local time
Today, 07:39
Joined
Jan 24, 2013
Messages
4
I have a form with 13 combo boxes in the header, each time you make a selection from one them the form filters and all the other combo boxes update their recordsets so they only display relevent values. This allows users to make a selection from any number of combo boxes and any combination of them to refine the search. Assuming thats what you want...
Each combo box has its row source set to a particular field and its after update event set to...
Code: 
Call subfilter
Then this is added to the form to filter it after each selection
Code: 
Public Sub subfilter()
    Dim bfilter, ifilter, cfilter, afilter, rfilter, dfilter, manfilter, modfilter, supfilter, pfilter, confilter, flfilter, rmfilter As String
    Dim bfilter1, ifilter1, cfilter1, afilter1, rfilter1, dfilter1, manfilter1, modfilter1, supfilter1, pfilter1, confilter1, flfilter1, rmfilter1 As String
    Dim bfilter2, ifilter2, cfilter2, afilter2, rfilter2, dfilter2, manfilter2, modfilter2, supfilter2, pfilter2, confilter2, flfilter2, rmfilter2 As String
 
    bfilter = Me.buildingcbo & ""
    ifilter = Me.itemcbo & ""
    cfilter = Me.ccbo & ""
    afilter = Me.acbo & ""
    rfilter = Me.rtextbox & ""
    dfilter = Me.dimcbo & ""
    manfilter = Me.mancbo & ""
    modfilter = Me.modcbo & ""
    supfilter = Me.supcbo & ""
    pfilter = Me.procbo & ""
    confilter = Me.concbo & ""
    flfilter = Me.flcbo & ""
    rmfilter = Me.rmcbo & ""
 
    bfilter1 = Chr(34) & bfilter & Chr(34)
    ifilter1 = Chr(34) & ifilter & Chr(34)
    cfilter1 = Chr(34) & cfilter & Chr(34)
    afilter1 = Chr(34) & afilter & Chr(34)
    rfilter1 = Chr(34) & rfilter & Chr(34)
    dfilter1 = Chr(34) & dfilter & Chr(34)
    manfilter1 = Chr(34) & manfilter & Chr(34)
    modfilter1 = Chr(34) & modfilter & Chr(34)
    supfilter1 = Chr(34) & supfilter & Chr(34)
    pfilter1 = Chr(34) & pfilter & Chr(34)
    confilter1 = Chr(34) & confilter & Chr(34)
    flfilter1 = Chr(34) & flfilter & Chr(34)
    rmfilter1 = Chr(34) & rmfilter & Chr(34)
 
    If bfilter = "" Then
        bfilter2 = "[Building] Like " & Chr(34) & "*" & Chr(34)
        Else: bfilter2 = "[Building] = " & bfilter1
    End If
 
    If ifilter = "" Then
        ifilter2 = "[Item] Like " & Chr(34) & "*" & Chr(34)
        Else: ifilter2 = "[Item] = " & ifilter1
    End If
 
    If cfilter = "" Then
        cfilter2 = "[Colour/Finish] Like " & Chr(34) & "*" & Chr(34)
        Else: cfilter2 = "[Colour/Finish] Like " & cfilter1
    End If
 
    If afilter = "" Then
        afilter2 = "[Asset number] Like " & Chr(34) & "*" & Chr(34)
        Else: afilter2 = "[Asset number] Like " & afilter1
    End If
 
    If rfilter = "" Then
        rfilter2 = "[Reserved for (Name)] Like " & Chr(34) & "*" & Chr(34)
        Else: rfilter2 = "[Reserved for (Name)] Like " & rfilter1
    End If
 
    If dfilter = "" Then
        dfilter2 = "[Dimensions] Like " & Chr(34) & "*" & Chr(34)
        Else: dfilter2 = "[Dimensions] Like " & dfilter1
    End If
 
    If modfilter = "" Then
        modfilter2 = "[Model] Like " & Chr(34) & "*" & Chr(34)
        Else: modfilter2 = "[Model] Like " & modfilter1
    End If
 
    If manfilter = "" Then
        manfilter2 = "[Manufacturer] Like " & Chr(34) & "*" & Chr(34)
        Else: manfilter2 = "[Manufacturer] Like " & manfilter1
    End If
 
    If supfilter = "" Then
        supfilter2 = "[Supplier] Like " & Chr(34) & "*" & Chr(34)
        Else: supfilter2 = "[Supplier] Like " & supfilter1
    End If
 
    If pfilter = "" Then
        pfilter2 = "[Project] Like " & Chr(34) & "*" & Chr(34)
        Else: pfilter2 = "[Project] Like " & pfilter1
    End If
 
    If confilter = "" Then
        confilter2 = "[Condition] Like " & Chr(34) & "*" & Chr(34)
        Else: confilter2 = "[Condition] Like " & confilter1
    End If
 
    If flfilter = "" Then
        flfilter2 = "[Floor] Like " & Chr(34) & "*" & Chr(34)
        Else: flfilter2 = "[Floor] Like " & flfilter1
    End If
 
    If rmfilter = "" Then
        rmfilter2 = "[Room number] Like " & Chr(34) & "*" & Chr(34)
        Else: rmfilter2 = "[Room number] Like " & rmfilter1
    End If
 
    Me.Filter = bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2
    Me.FilterOn = True
 
    Call cboupdate
 
End Sub

and this will update the row source of all the combo boxes to keep them relevant

Code: 
Public Sub cboupdate()
    Dim sql2 As String
    Dim sql4 As String
    Dim sql6 As String
    Dim sql8 As String
    Dim sql10 As String
    Dim sql12 As String
    Dim sql14 As String
    Dim sql16 As String
 
    Dim sql18 As String
    Dim sql20 As String
    Dim sql22 As String
    Dim sql24 As String
    Dim sql26 As String
 
    Dim bfilter, ifilter, cfilter, afilter, rfilter, dfilter, manfilter, modfilter, supfilter, pfilter, confilter, flfilter, rmfilter As String
    Dim bfilter1, ifilter1, cfilter1, afilter1, rfilter1, dfilter1, manfilter1, modfilter1, supfilter1, pfilter1, confilter1, flfilter1, rmfilter1 As String
    Dim bfilter2, ifilter2, cfilter2, afilter2, rfilter2, dfilter2, manfilter2, modfilter2, supfilter2, pfilter2, confilter2, flfilter2, rmfilter2 As String
 
    bfilter = Me.buildingcbo & ""
    ifilter = Me.itemcbo & ""
    cfilter = Me.ccbo & ""
    afilter = Me.acbo & ""
    rfilter = Me.rtextbox & ""
    dfilter = Me.dimcbo & ""
    manfilter = Me.mancbo & ""
    modfilter = Me.modcbo & ""
    supfilter = Me.supcbo & ""
    pfilter = Me.procbo & ""
    confilter = Me.concbo & ""
    flfilter = Me.flcbo & ""
    rmfilter = Me.rmcbo & ""
 
    bfilter1 = Chr(34) & bfilter & Chr(34)
    ifilter1 = Chr(34) & ifilter & Chr(34)
    cfilter1 = Chr(34) & cfilter & Chr(34)
    afilter1 = Chr(34) & afilter & Chr(34)
    rfilter1 = Chr(34) & rfilter & Chr(34)
    dfilter1 = Chr(34) & dfilter & Chr(34)
    manfilter1 = Chr(34) & manfilter & Chr(34)
    modfilter1 = Chr(34) & modfilter & Chr(34)
    supfilter1 = Chr(34) & supfilter & Chr(34)
    pfilter1 = Chr(34) & pfilter & Chr(34)
    confilter1 = Chr(34) & confilter & Chr(34)
    flfilter1 = Chr(34) & flfilter & Chr(34)
    rmfilter1 = Chr(34) & rmfilter & Chr(34)
 
    If bfilter = "" Then
        bfilter2 = "[Building] Like " & Chr(34) & "*" & Chr(34)
        Else: bfilter2 = "[Building] = " & bfilter1
    End If
 
    If ifilter = "" Then
        ifilter2 = "[Item] Like " & Chr(34) & "*" & Chr(34)
        Else: ifilter2 = "[Item] = " & ifilter1
    End If
 
    If cfilter = "" Then
        cfilter2 = "[Colour/Finish] Like " & Chr(34) & "*" & Chr(34)
        Else: cfilter2 = "[Colour/Finish] Like " & cfilter1
    End If
 
    If afilter = "" Then
        afilter2 = "[Asset number] Like " & Chr(34) & "*" & Chr(34)
        Else: afilter2 = "[Asset number] Like " & afilter1
    End If
 
    If rfilter = "" Then
        rfilter2 = "[Reserved for (Name)] Like " & Chr(34) & "*" & Chr(34)
        Else: rfilter2 = "[Reserved for (Name)] Like " & rfilter1
    End If
 
    If dfilter = "" Then
        dfilter2 = "[Dimensions] Like " & Chr(34) & "*" & Chr(34)
        Else: dfilter2 = "[Dimensions] Like " & dfilter1
    End If
 
    If modfilter = "" Then
        modfilter2 = "[Model] Like " & Chr(34) & "*" & Chr(34)
        Else: modfilter2 = "[Model] Like " & modfilter1
    End If
 
    If manfilter = "" Then
        manfilter2 = "[Manufacturer] Like " & Chr(34) & "*" & Chr(34)
        Else: manfilter2 = "[Manufacturer] Like " & manfilter1
    End If
 
    If supfilter = "" Then
        supfilter2 = "[Supplier] Like " & Chr(34) & "*" & Chr(34)
        Else: supfilter2 = "[Supplier] Like " & supfilter1
    End If
 
    If pfilter = "" Then
        pfilter2 = "[Project] Like " & Chr(34) & "*" & Chr(34)
        Else: pfilter2 = "[Project] Like " & pfilter1
    End If
 
    If confilter = "" Then
        confilter2 = "[Condition] Like " & Chr(34) & "*" & Chr(34)
        Else: confilter2 = "[Condition] Like " & confilter1
    End If
 
    If flfilter = "" Then
        flfilter2 = "[Floor] Like " & Chr(34) & "*" & Chr(34)
        Else: flfilter2 = "[Floor] Like " & flfilter1
    End If
 
    If rmfilter = "" Then
        rmfilter2 = "[Room number] Like " & Chr(34) & "*" & Chr(34)
        Else: rmfilter2 = "[Room number] Like " & rmfilter1
    End If
 
    sql2 = "SELECT DISTINCT Furniture.Building From Furniture WHERE " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql4 = "SELECT DISTINCT Furniture.Item From Furniture WHERE " & bfilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql6 = "SELECT DISTINCT Furniture.[Reserved for (Name)] From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql8 = "SELECT DISTINCT Furniture.Manufacturer From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql10 = "SELECT DISTINCT Furniture.Model From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql12 = "SELECT DISTINCT Furniture.Supplier From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql14 = "SELECT DISTINCT Furniture.Project From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql16 = "SELECT DISTINCT Furniture.Condition From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql18 = "SELECT DISTINCT Furniture.Dimensions From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql20 = "SELECT DISTINCT Furniture.[Colour/Finish] From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql22 = "SELECT DISTINCT Furniture.[Asset number] From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & " AND " & rmfilter2 & ";"
    sql24 = "SELECT DISTINCT Furniture.Floor From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & rmfilter2 & ";"
    sql26 = "SELECT DISTINCT Furniture.[Room number] From Furniture WHERE " & bfilter2 & " AND " & ifilter2 & " AND " & cfilter2 & " AND " & afilter2 & " AND " & rfilter2 & " AND " & dfilter2 & " AND " & manfilter2 & " AND " & modfilter2 & " AND " & supfilter2 & " AND " & pfilter2 & " AND " & confilter2 & " AND " & flfilter2 & ";"
 
    Me.buildingcbo.RowSource = sql2
    Me.itemcbo.RowSource = sql4
    Me.rtextbox.RowSource = sql6
    Me.mancbo.RowSource = sql8
    Me.modcbo.RowSource = sql10
    Me.supcbo.RowSource = sql12
    Me.procbo.RowSource = sql14
    Me.concbo.RowSource = sql16
    Me.dimcbo.RowSource = sql18
    Me.ccbo.RowSource = sql20
    Me.acbo.RowSource = sql22
    Me.flcbo.RowSource = sql24
    Me.rmcbo.RowSource = sql26
 
End Sub

The down side to this is that every record must have a value in the field for this to work properly, run an update query to put "N/A" in any blank fields.
 
D

DavidAtWork

Registered User.
Local time
Today, 07:39
Joined
Oct 25, 2011
Messages
699
There are multiple ways to do this and you have to decide from a design POV how you want it to work.
I would suggest the easiest and least complicated would be to have your 2 combo boxes and a button labelled "Search" and the On_Click event will trigger a requery of whatever is displaying the results. The recordsource for your form will be a vba SQL statement with the criteria from your combo boxes. The criteria will be a string variable depending on whether one or both combo boxes have been selected.

Another way would be to create 2 queries, one with criteria as per combo 1 and another with criteria from both combos and you set the recordsource this way by checking if combo 2 Is Null

Either way, the key is to check if the combo boxes have a value before you requery your results display and base your search criteria on the users selection(s)

David
 
You must log in or register to reply here.

Similar threads

C
Solved combo with 2 control sources
2
Replies
34
Views
742
Gasman
Gasman
I
Cascade combobox on continuous form
Replies
8
Views
356
MajP
MajP
D
Solved Synchronising a List Box to a Combo Box
Replies
9
Views
784
davegoodo
D
L
I need to be able to type in text box and for the list box to show me all similar text. No VBA )-:
Replies
8
Views
491
Pat Hartman
P
S
Unbound combo box is clear after making selection
Replies
10
Views
279
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom