Im tryin something and i rly rly need help

[zeroskrull]

I want to know if any1 have an idea to filter my datagrid with this checkboxes

Sub listevents()

With Form3
    If .Text1.Text <> "" Then namefilter = "%" + Trim(.Text1.Text) + "%" Else namefilter = ""
    lifefilter = .Text3.Text
    
    For i = 0 To .List1.ListCount - 1
        .List1.ListIndex = i
        If .List1.Selected(i) = True Then
            If abilitiesfilter = "" Then abilitiesfilter = "%" + .List1.Text + "%" Else abilitiesfilter = abilitiesfilter + "' Or Abilities LIKE '" + "%" + .List1.Text + "%"
        End If
    Next i

    For i = 0 To .List2.ListCount - 1
        .List2.ListIndex = i
        If .List2.Selected(i) = False Then
            If teamfilter = "" Then teamfilter = "%" + .List2.Text + "%" Else teamfilter = teamfilter + "' And Team NOT LIKE '" + "%" + .List2.Text + "%"
        End If
    Next i

    For i = 0 To .List3.ListCount - 1
        .List3.ListIndex = i
        If .List3.Selected(i) = False Then
            If setfilter = "" Then setfilter = .List3.Text Else setfilter = setfilter + "', '" + .List3.Text
        End If
    Next i
    
'    .Text11.Text = teamfilter
'    .Text12.Text = abilitiesfilter
    
        
    oRs.Close
    
    '01 STAN X----
    If setfilter <> "" And teamfilter = "" And abilitiesfilter = "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '02 STAN -X---
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter = "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Team NOT LIKE '" & teamfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '03 STAN --X--
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter <> "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Abilities LIKE '" & abilitiesfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '04 STAN ---X-
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter = "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '05 STAN XX---
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter = "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '06 STAN -XX--
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '07 STAN --XX-
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '08 STAN X--X-
    ElseIf setfilter <> "" And teamfilter = "" And abilitiesfilter = "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '09 STAN X-X--
    ElseIf setfilter <> "" And teamfilter = "" And abilitiesfilter <> "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Abilities LIKE '" & abilitiesfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '10 STAN -X-X-
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter = "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Team not LIKE '" & teamfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '11 STAN XXX--
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '12 STAN -XXX-
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '13 STAN X-XX-
    ElseIf setfilter <> "" And teamfilter = "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '14 STAN XX-X-
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter = "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '15 STAN XXXX-
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '16 STAN -----
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter = "" And namefilter = "" And lifefilter = 0 Then
        oRs.Open "select * from heroclixmain ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '17 STAN ----X
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter = "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '18 STAN ---XX
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter = "" And namefilter <> "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Name LIKE '" & namefilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '19 STAN X---X
    ElseIf setfilter <> "" And teamfilter = "" And abilitiesfilter = "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '20 STAN -X--X
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter = "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Team not LIKE '" & teamfilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '21 STAN --X-X
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter <> "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Abilities LIKE '" & abilitiesfilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '23 STAN XX--X
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter = "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '24 STAN -XX-X
    ElseIf setfilter = "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter = "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '25 STAN --XXX
    ElseIf setfilter = "" And teamfilter = "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    '26 STAN XXXXX
    ElseIf setfilter <> "" And teamfilter <> "" And abilitiesfilter <> "" And namefilter <> "" And lifefilter <> 0 Then
        oRs.Open "select * from heroclixmain where Set Not IN ('" & setfilter & "') AND Team not LIKE '" & teamfilter & "' AND Abilities LIKE '" & abilitiesfilter & "' AND Name LIKE '" & namefilter & "' AND Points = " & lifefilter & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic
    End If
    
      
    Set .DataGrid1.DataSource = oRs
    
End With
End Sub

]

[zeroskrull]

The bug is when i select 1 team, 1 abilitie, 1 set and then when i pick another abilitie all the thing start buggin even when the consult said no infinity chanllenge (the items still popin up )

[DataMiser]

Hard to make heads or tails out of that jumble of unformatted code.

The code could be simplified and made easier to read by altering the structure of the IF, ElseIF block. For example it looks like all of them are expecting SetFilter to either be blank or have a value would be much more readable if it was broken out from all the And statements like

If setfilter = "" Then
    'code where setfilter should be blank all would go here
Else
    'code where setfilter not blank would all go here
End If

You could perhaps do something similar with other parts making the code more readable and a bit more efficent.
This of course would not solve your problem but may make it easier to locate.

To find the issue you should set a breakpoint and step through the code line by line to see what is happening.

[zeroskrull]

well i have 4 ways to filter the datagrid...but how i can do that?
so when 1 is empty maybe can be full in many options and empty in others

[Doogle]

Your 'listevents' routine doesn't look as if it's serialy reusable since there's no indication that you are initialising 'namefilter', 'abilitiesfilter', 'teamfilter' and 'setfilter', so on second and subsequent calls they will start with whatever was in them at the end of the previous call. (This is all on the assumption that they are actually defined somewhere)

[zeroskrull]

in the start of the event
at the end of the use all variables clean on "" or 0 only points is integer the rest are string

If .Text1.Text <> "" Then namefilter = "%" + Trim(.Text1.Text) + "%" Else namefilter = ""
lifefilter = .Text3.Text

For i = 0 To .List1.ListCount - 1
.List1.ListIndex = i
If .List1.Selected(i) = True Then
If abilitiesfilter = "" Then abilitiesfilter = "%" + .List1.Text + "%" Else abilitiesfilter = abilitiesfilter + "' Or Abilities LIKE '" + "%" + .List1.Text + "%"
End If
Next i

For i = 0 To .List2.ListCount - 1
.List2.ListIndex = i
If .List2.Selected(i) = False Then
If teamfilter = "" Then teamfilter = "%" + .List2.Text + "%" Else teamfilter = teamfilter + "' And Team NOT LIKE '" + "%" + .List2.Text + "%"
End If
Next i

For i = 0 To .List3.ListCount - 1
.List3.ListIndex = i
If .List3.Selected(i) = False Then
If setfilter = "" Then setfilter = .List3.Text Else setfilter = setfilter + "', '" + .List3.Text
End If
Next i

[zeroskrull]

if some1 have a good example of filterin a datagrid or a sql with checkboxes would be awsome....now u know my interface maybe some1 have an idea or how to solve or maybe an example somewhere

EDIT:
maybe in other language (programming language)

[Doogle]

I'd simplify the code and build the parts of the SQL dynamically depending upon what's been selected as filters.

Dim setSQL As String
Dim teamSQL As String
Dim abilitiesSQL As String
Dim nameSQL As String
Dim lifeSQL As String
Dim setFilter As String
Dim teamFilter As String
Dim abilitiesFilter As String
Dim nameFilter As String
Dim lifeFilter As Long
Dim intI As Integer
setSQL = "Set Not IN ('"
teamSQL = "Team NOT LIKE '"
abilitiesSQL = "Abilities LIKE '"
nameSQL = "Name LIKE '"
lifeSQL = "Points = "
With form3
    lifeFilter = lifeSQL & Val(.text3.Text)
    If text1.Text <> vbNullString Then
        nameFilter = nameSQL & "%" & Trim$(.text1.Text) & "%"
    End If
    For intI = 0 To .list1.ListCount - 1
        If .list1.Selected(intI) = True Then
            abilitiesFilter = abilitiesFilter & "%" & .list1.List(intI) & "%" & " OR " & abilitiesSQL
        End If
    Next intI
    If abilitiesFilter <> vbNullString Then
        abilitiesFilter = Left$(abilitiesSQL & abilitiesFilter, Len(abilitiesFilter) - Len(abilitiesSQL + 4))
    End If
    For intI = 0 To .list2.ListCount - 1
        If .list2.Selected(intI) = True Then
            teamFilter = teamFilter & "%" & .list2.List(intI) & "%" & " AND " & teamSQL
        End If
    Next intI
    If teamFilter <> vbNullString Then
        teamFilter = Left$(teamSQL & teamFilter, Len(teamFilter) - Len(teamSQL + 5))
    End If
    For intI = 0 To .list3.ListCount - 1
        If .list3.Selected(intI) = True Then
            setFilter = setFilter & .list3.List(intI) & ","
        End If
    Next intI
    If setFilter <> vbNullString Then
        setFilter = setSQL & Left(setFilter, Len(setFilter) - 1) & ")"
    End If
    strSQL = "SELECT * FROM heroclixmain WHERE "
    strSQL = strSQL & lifeFilter
    If nameFilter <> vbNullString Then strSQL = strSQL & " AND (" & nameFilter & ")"
    If abilitiesFilter <> vbNullString Then strSQL = strSQL & " AND (" & abilitiesFilter & ")"
    If teamFilter <> vbNullString Then strSQL = strSQL & " AND (" & teamFilter & ")"
    If setFilter <> vbNullString Then strSQL = strSQL & " AND (" & setFilter & ")"
    strSQL = strSQL & " ORDER BY Code"
    oRs.Open strSQL, adoConn, adOpenStatic, adLockOptimistic
    Set .DataGrid1.DataSource = oRs
End With
End Sub

Obviously not tested in any way, but hopefully you get the idea.

[MarkT]

I too would build the where clause dynamically if needed. How you are setting values for namefilter and set filter seem to make sense. I'm not sure about your abilitiesfilter and teamfilter. I would have to see the database to know if those make sense. As far as building the where clause I would do something like this (assuming the value for you variables are correct).

Dim strCondtions() As String
Dim strWhere As String
Dim i As Index
    
    i = -1
    
    ' Get your where clauses
    If setfilter <> "" Then
        i = i + 1
        ReDim Preserve strCondtions(i)
        strCondtions(i) = " Set Not IN ('" & setfilter & "') "
    End If

    If teamfilter <> "" Then
        i = i + 1
        ReDim Preserve strCondtions(i)
        strCondtions(i) = " NOT LIKE '" & teamfilter & "' "
    End If

    If abilitiesfilter <> "" Then
        i = i + 1
        ReDim Preserve strCondtions(i)
        strCondtions(i) = " Abilities LIKE '" & abilitiesfilter & "' "
    End If
    
    If namefilter <> "" Then
        i = i + 1
        ReDim Preserve strCondtions(i)
        strCondtions(i) = " Name LIKE '" & namefilter & "' "
    End If
    
    If lifefilter <> 0 Then
        i = i + 1
        ReDim Preserve strCondtions(i)
        strCondtions(i) = " Points = " & lifefilter & " "
    End If
    
    If i > -1 Then
        strWhere = "Where " & Join(strCondtions, "And")
    End If
    
    
    oRs.Open "select * from heroclixmain " & strWhere & " ORDER BY Code", adoConn, adOpenStatic, adLockOptimistic

[zeroskrull]

the value on the Abilities are with "," (a character can have: Flurry, Charge - i think u can see it on the picture) so a character can have more than one abilities same with the team (a character can have: SHIELD, Avengers - i think u can see it on the picture) thats why im usin like and a lil wildcard "%" at the begin an ends