dcsimg
Results 1 to 6 of 6

Thread: How to filter using multiple checkboxes in VB 6?

  1. #1

    Thread Starter
    Registered User
    Join Date
    Jul 2013
    Posts
    3

    How to filter using multiple checkboxes in VB 6?

    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

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,281

    Re: How to filter using multiple checkboxes in VB 6?

    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.

  3. #3
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: How to filter using multiple checkboxes in VB 6?

    Download

    Using the CODE wrapper, your code looks like

    Code:
    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

    Code:
    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
    Last edited by Spoo; Jul 10th, 2013 at 10:25 AM. Reason: add blue color

  4. #4

    Thread Starter
    Registered User
    Join Date
    Jul 2013
    Posts
    3

    Re: How to filter using multiple checkboxes in VB 6?

    Quote Originally Posted by Spoo View Post
    Download

    Using the CODE wrapper, your code looks like

    Code:
    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

    Code:
    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

  5. #5

    Thread Starter
    Registered User
    Join Date
    Jul 2013
    Posts
    3

    Re: How to filter using multiple checkboxes in VB 6?

    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

  6. #6
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: How to filter using multiple checkboxes in VB 6?

    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.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width