How to filter using multiple checkboxes in VB 6?

[download]

Hi im just starting to explore VB 6 and my project is a database with multiple filtering function using checkboxes. I was able to filter the data with multiple checkboxes but how will I make my code shorter since I have like 15 checkboxes. My code below will only work for a single combination of the checkboxes. Thank you in advance guys.

If check1.Value = 1 Then
If check2.Value = 1 Then
If check3.Value = 1 Then
If check4.Value = 1 Then
If check5.Value = 1 Then

s = -1
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from table1 where value1 and value2 and value3 and value4 and value5 like '" & s & "%'", cn, adOpenKeyset, adLockOptimistic, adCmdText
Set DataGrid1.DataSource = rs
Set rs = Nothing

End If
End If
End If
End If
End If

[SamOscarBrown]

Check out how to set up and use an ARRAY of checkboxes. By using an Array, you can use a loop (loops) to do what you want.

[Spoo]

Download

Using the CODE wrapper, your code looks like

If check1.Value = 1 Then
    If check2.Value = 1 Then
        If check3.Value = 1 Then
            If check4.Value = 1 Then
                If check5.Value = 1 Then
                    s = -1
                    Set rs = New ADODB.Recordset
                    rs.CursorLocation = adUseClient
                    rs.Open "Select * from table1 where value1 and value2 and value3 and value4 and value5 like '" & s & "%'", cn, adOpenKeyset, adLockOptimistic, adCmdText
                    Set DataGrid1.DataSource = rs
                    Set rs = Nothing
                End If
            End If
        End If
    End If
End If

Perhaps you could build the WHERE clause in your IF statements.
Assuming you want something like ,,,

WHERE value1 AND value2 AND value3

,,, then maybe something along these lines may overcome the "single combination" issue

strWHERE = ""
If check1.Value = 1 Then strWHERE = strWHERE & value1
If check2.Value = 1 Then strWHERE = strWHERE & IIf(Len(strWHERE) > 0, " AND ","") & value2
If check3.Value = 1 Then strWHERE = strWHERE & IIf(Len(strWHERE) > 0, " AND ","") & value3
'
s = -1
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from table1 WHERE " & strWHERE & " like '" & s & "%'", cn, adOpenKeyset, adLockOptimistic, adCmdText
Set DataGrid1.DataSource = rs
Set rs = Nothing

The above is essentially pseudo-code, but it might give you some ideas.

Spoo

[download]

Download

Using the CODE wrapper, your code looks like

If check1.Value = 1 Then
    If check2.Value = 1 Then
        If check3.Value = 1 Then
            If check4.Value = 1 Then
                If check5.Value = 1 Then
                    s = -1
                    Set rs = New ADODB.Recordset
                    rs.CursorLocation = adUseClient
                    rs.Open "Select * from table1 where value1 and value2 and value3 and value4 and value5 like '" & s & "%'", cn, adOpenKeyset, adLockOptimistic, adCmdText
                    Set DataGrid1.DataSource = rs
                    Set rs = Nothing
                End If
            End If
        End If
    End If
End If

Perhaps you could build the WHERE clause in your IF statements.
Assuming you want something like ,,,

WHERE value1 AND value2 AND value3

,,, then maybe something along these lines may overcome the "single combination" issue

strWHERE = ""
If check1.Value = 1 Then strWHERE = strWHERE & value1
If check2.Value = 1 Then strWHERE = strWHERE & IIf(Len(strWHERE) > 0, " AND ","") & value2
If check3.Value = 1 Then strWHERE = strWHERE & IIf(Len(strWHERE) > 0, " AND ","") & value3
'
s = -1
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from table1 WHERE " & strWHERE & " like '" & s & "%'", cn, adOpenKeyset, adLockOptimistic, adCmdText
Set DataGrid1.DataSource = rs
Set rs = Nothing

The above is essentially pseudo-code, but it might give you some ideas.

Spoo

Hi Spoo,

Thanks for the reply. Value1, value2, value3 is the actual fields from the table.

Thanks

[download]

Hi Samoscarbrown,

Thanks for the reply. I tried to look for your suggestion but I cant figure out how to do it. Sorry im very new to VB.
Appreciate if you can show me some codes.

Thanks in advance

[Doogle]

There is a similar thread here http://www.vbforums.com/showthread.p...-query-problem which was using TextBoxes rather than CheckBoxes but the principle is the same.