1 Attachment(s)
Im tryin something and i rly rly need help
Attachment 90385
I want to know if any1 have an idea to filter my datagrid with this checkboxes
Code:
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
]
Re: Im tryin something and i rly rly need help
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 )
Re: Im tryin something and i rly rly need help
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
Code:
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.
Re: Im tryin something and i rly rly need help
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
Re: Im tryin something and i rly rly need help
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)
Re: Im tryin something and i rly rly need help
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
Re: Im tryin something and i rly rly need help
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)
Re: Im tryin something and i rly rly need help
I'd simplify the code and build the parts of the SQL dynamically depending upon what's been selected as filters.
Code:
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.
Re: Im tryin something and i rly rly need help
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).
Code:
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
Re: Im tryin something and i rly rly need help
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
