Populating Listbox Using Multi Combo Box Selection

[LarryB]

Hi all,

I have a listbox on my form which initially pulls in data using a query as it's rowsource.

To help with filtering, I have put numerous comboboxes on the form. The comboboxes are populated using data in two other tables.

Once a selection is made, using the SQL in my query mentioned above a set the Listbox rowsource to that of the query as well as the combobox selection on the form.

lstDistributionDetails.RowSource = "SELECT tblMain.A_Code, tblA.A_Name, tblMain.ISIN, tblF._F_Code, tblF.F_Name, tblFunds.Type, tblMain.Status FROM tblF INNER JOIN (tblA INNER JOIN tblMain ON tblA.[A_Code] = tblMain.[A_Code]) ON tblF.[ISIN] = tblMain.[ISIN] WHERE ((tblMain.A_Code)=[forms]![frmMain]![cboAgent])"

This is fine, but as I am dealing with Multo Comboboxes, I went further using the same SQL query, and added the form value of the second combobox

 SELECT tblMain.A_Code, tblA.A_Name, tblMain.ISIN, tblF._F_Code, tblF.F_Name, tblFunds.Type, tblMain.Status
FROM tblF INNER JOIN (tblA INNER JOIN tblMain ON tblA.[A_Code] = tblMain.[A_Code]) ON tblF.[ISIN] = tblMain.[ISIN]
WHERE (((tblMain.A_Code)=[FORMS]![frmMain]![cboAgent]) AND ((tblMain.ISIN)=[FORMS]![frmMain]![cboISIN]))

Again this is fine, but because of the And operand, I must have a selection in both comboboxes.

How do I cater for blank comboboxes? Therefor, show me all records when I have a selection in the first or second combobox? Basically a Select *

If I change the operand to OR, it will just show me all records based on my selections

 

[pbaldy]

Since it looks like you're building the SQL in code, only include a combo if it has a value.

 

[LarryB]

Hi Pbaldy, thanks for your reply

I was thinking along those lines alright, using an If statement, but I should of mentioned I have 7 comboboxes on the form! That would be a lot of SQL combinations, based on true or false values across the 7 combos

Is this what you meant?

Cheers

 

[pbaldy]

I assume they're for different fields? I'd simply test each one at a time and add to the SQL statement for each one that's filled in. What does your code look like now? The sample db here demonstrates the type of thing I'm talking about.

 

[LarryB]

Good morning PBaldy,

I have decided to loop through all comboboxes on my form and identify the name and if the value is not isnull. This works perfectly, but I still feel it is overkill, it is what the user wants, as an option I will also put in a textbox and loop through the recordset and show the record in the list box

Below is the code behind my button. In the if statement you can see "" this is where I will put in my SQL to add to the rowsource, as they are different fields, I need to put in each one separately.

Messy, but it works!

Dim CTRL            As Control
Dim sRowSource      As String
Dim sRowAddition    As String
  
 sRowSource = "SELECT tblMain.A_Code, tblA.A_Name, tblMain.ISIN, tblF.MF_F_Code, tblF.F_Name, tblF.Type, tblMain.Status" & _
              "FROM tblF INNER JOIN (tblA INNER JOIN tblMain ON tblA.[A_Code] = tblMain.[A_Code]) ON tblF.[ISIN] = tblMain.[ISIN]"
  
 'MsgBox sRowSource
  
 For Each CTRL In Me.Controls
        If CTRL.ControlType = acComboBox Then
            If Not IsNull(CTRL) Then
                'MsgBox CTRL.Name
                If CTRL.Name = "cboA" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "cbostatus" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "cboAName" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "CBOISIN" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "cboMF" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "cboFName" Then
                    sRowAddition = sRowAddition & ""
                ElseIf CTRL.Name = "cboType" Then
                    sRowAddition = sRowAddition & ""
            End If
        End If
Next
    
sRowSource = sRowSource & "WHERE (" & sRowAddition & ")"
lstDistributionDetails.RowSource = sRowSource
 lstDistributionDetails.Requery

 

[LarryB]

I meant to ask, you said there was a sample db but I don't see it. Do you have a linky?

 

[LarryB]

UPDATE:

The above code does not cater for my "AND" statement if I am dealing with multi criteria

For each combo item selected I have included AND - The criteria changes for each Combobox

 sRowAddition = sRowAddition & " AND ((tblMain.A_Code)=[Forms]![frmMain]![cboA])"

Following through the remainder of the code we have a problem where you merge the srowsource and srowaddition strings, when you select multi combox

srowaddition will begin with " AND "

Merging the two the strings become ....WHERE (AND ..... which will result in an error.

Using string manipulation MID() I strip out the first 5 characters - " AND "

The end of my code now looks like this

 'remove the first 5 characters of the string removes the first ' AND ' which is not required in the string after WHERE
sRowAddition = Mid([sRowAddition], 6, Len([sRowAddition]))
    
If sRowAddition = "" Then
    sRowSource = sRowSource
Else
    sRowSource = sRowSource & " WHERE (" & sRowAddition & ")"
End If
 lstDistributionDetails.RowSource = sRowSource
 lstDistributionDetails.Requery

This code is now a function and I am calling it from the afterupdate event of each combobox

I am eager to see this working when I have a tonne of data

Cheers!

 

[pbaldy]

I meant to ask, you said there was a sample db but I don't see it. Do you have a linky?

Sorry, forgot to paste the link:

http://www.baldyweb.com/BuildSQL.htm

but it sounds like you got it working.