Filter ADO recordset 1

[jozino01]

i have one field "Type" in ADO recordset (reading text field from Access table and showing it in datagrid)

this field can have values like (xxx is extension/number):
ASE xxx
ASD xxx
ASV xxx
TSE xxx
TSD xxx
TSV xxx

i have five checkboxes on form (chAS, chTS, chE, chV, chD) - as default all have value 1 (checked) and all data are displayed.
i want to set filter for ado recordset, so an user should be able to select dislayed record in datagrid by checking/unchecking these check boxes.

my code is now like:
If chTS.Value = 0 Then
rs3.Filter = "Type='AS*'"
DataGrid2.Refresh
End If

If chAS.Value = 0 Then
rs3.Filter = "Type='TS*'"
DataGrid2.Refresh
End If

If chE.Value = 0 Then
rs3.Filter = "Type='ASD*' OR Type='ASV*'"
DataGrid2.Refresh
End If

If chD.Value = 0 Then
rs3.Filter = "Type='ASE*' OR Type='ASV*'"
DataGrid2.Refresh
End If

If chV.Value = 0 Then
rs3.Filter = "Type='ASD*' OR Type='ASE*'"
DataGrid2.Refresh
End If

but only first two are working.

what would be the best way to do it properly? any ideas, please?

 

[RonRepp]

J01:

I believe the synatax has to be like this:

If chE.Value = 0 Then
    rs3.Filter = "Type='ASD*'" OR rs3.Filter = "Type='ASV*'"
    DataGrid2.Refresh
    End If
[/close]

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Andrzejek]

I am afraid your code adds one Filter to another Filter, so if none of the boxes are check, it works like:

rs3.Filter = "Type='AS*'"
the resoult is then filtered by
rs3.Filter = "Type='AS*'"
the resoult is then filtered by
rs3.Filter = "Type='ASD*' OR Type='ASV*'"
... and so on.

You stack your filters. Is that what you want to do?

Also, do you somewhere remove Filter from your rs3?


Have fun.

---- Andy

 

[jozino01]

thanks for your input.
to Andy - well, i reset recordset (Set rs3 = Nothing) each time before datagrid is populated. do i need to reset the filter as well?

stacking filters - user should be at least able to select records in two levels:
- first select ASx or TSx records
- second select/remove any combination of E, D, V
- if it allows to select/remove more options/variants it would be optional

 

[Andrzejek]

If you set the rs3 = Nothing, that should destroy and Filter to that recordset (I guess here), but that's also defeats the purpose of the Filter :-(

Why don't you

rs3.Filter = adodb.FilterGroupEnum.adFilterNone

Have fun.

---- Andy

 

[jozino01]

thanks; you are right i messed it a bit...

 

[BobRodes]

<rs3.Filter = "Type='ASD*'" OR rs3.Filter = "Type='ASV*'"
Ron, the OR operator can only conjoin two items that can evaluate to binary values. An assignment of an object property didn't seem to fit the bill, and the result would simply be a binary number, even if it did work. Anyway, my test returns a Type Mismatch as I suspected it would.

<I am afraid your code adds one Filter to another Filter
Andy, I've never heard of stacking filters, so this was news to me. In fact, I didn't think it was the case, since I understand filters to be simply a valid WHERE clause in SQL. So, I did a little experiment. From my debug window, using NWIND.Customers as the recordset:

rs.Filter = "companyname like 'A*'"
? rs.Filter
companyname like 'A*'
? rs.Fields(1)
Ana Trujillo Emparedados y helados
rs.Filter = "companyname like 'B*'"
? rs.Filter
companyname like 'B*'
? rs.Fields(1)
Berglunds snabbköp

At which point I took a better look at joz's code, and realized that I didn't understand what you were saying at all...but filters do NOT stack.

jozino, let's see if we can fix your code. First, I would NOT use any sort of logic that ties one checkbox with the filter from another checkbox, as you do. (In other words, don't say type is AS if you don't want TS, say type is AS if you DO want AS. And so on.) Find a way to tie each checkbox with the type that it's working with. Next, you have to find a way to preserve the existing filter selections. Finally, you have to include logical OR logic in your filter properly.

Ok. To get your filter the way you need it, you need to recreate it from scratch every time the user changes a check box. Don't try to keep what was there before and add onto it; that's an order of magnitude more complicated. Here's a proof of concept. Put a hierarchical flexgrid on a form, along with 5 checkboxes in an array. (In the code below, I called them all chkCrit.) Add a label if you want to see the current state of the filter property. Add the following code:

   Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fString(4) As String

Private Sub chkCrit_Click(Index As Integer)
Dim i As Integer
Dim strFilter As String
Dim chkCount As Integer
For i = 0 To 4
    If chkCrit(i) = vbChecked Then
        chkCount = chkCount + 1
        strFilter = IIf(strFilter = "", fString(i), strFilter & " or " & fString(i))
    End If
Next i
Select Case chkCount
    Case 0
        rs.Filter = "companyname = Null"
    Case 5
        rs.Filter = ""
    Case Else
        rs.Filter = strFilter
End Select
Label1 = rs.Filter
End Sub

Private Sub Form_Load()
fString(0) = "companyname like 'A*'"
fString(1) = "companyname like 'B*'"
fString(2) = "companyname like 'C*'"
fString(3) = "companyname like 'D*'"
fString(4) = "companyname like 'E*'"

Dim strCn As String
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
Set cn = New ADODB.Connection
cn.Open strCn

Set rs = New ADODB.Recordset
With rs
    .Open "select * from customers", cn, adOpenStatic
End With

Set MSHFlexGrid1.DataSource = rs
End Sub

You should be able to take this code and modify it to suit your purposes. Post back if you can't.

Now, one more thing. You haven't said what your back end database is. If it's Access, your filter won't work the way you want it to, because you have to use the LIKE operator to use the * wildcard. Otherwise, it will be interpreted literally. If it isn't Access, suppose you let us know what it is so we can tell you what a valid WHERE clause would be in its particular flavor of SQL.

HTH

Bob

 

[jozino01]

thanks bob.
yes, back end database is access.
i deleted my code.
for now, i am using only two check boxes and one button to apply filter.

Private Sub Command3_Click()

If chTS.Value = 1 Then
rs3.Filter = "Type LIKE 'TS*'"
DataGrid2.Refresh
rs3.Filter = adodb.FilterGroupEnum.adFilterNone
End If

If chAS.Value = 1 Then
rs3.Filter = "Type LIKE 'AS*'"
DataGrid2.Refresh
rs3.Filter = adodb.FilterGroupEnum.adFilterNone
End If

End Sub

but the code is not working; after click datagrid gets empty regardless of checkboxes selection, even if i check both options.
???

 

[Andrzejek]

Bob thanks for the correction. You are right, I was wrong - ADODB Filters do not 'stack'. I guess I can learn something new even on Friday afternoon

jozino01

If chTS.Value = 1 Then
    rs3.Filter = "Type LIKE 'TS*'"
    [blue]DataGrid2.Refresh[/blue]
    rs3.Filter = adodb.FilterGroupEnum.adFilterNone
End If

Since you are using ADODB recordset, are you hoping to Refresh your Grid2 this way? I am under the impression that ADODB recordsets are used to populate Grid by:

If chTS.Value = 1 Then
    rs3.Filter = "Type LIKE 'TS*'"
    [blue]DataGrid2.Clear
    For i = 1 To rs3.RecordCount
        DataGrid2.AddItem rs3!FieldName
        rs3.MoveNext
    Next i
[/blue]
    [green]'You do not need this line, see above[/green]
    [green]'rs3.Filter = adodb.FilterGroupEnum.adFilterNone[/green]
End If

Have fun.

---- Andy

 

[jozino01]

well, i populate datagrid with this code:

sSQL = "Select [Type], [Length], [Load], [BPrice]/[Length]/" & cmbSpan.Text & " as Sqf, [BPrice] From FSFrame Where [Width]=" & cmbSpan.Text & " And [Load]>" & 0.95 * load & " Order by [Load];"
Set rs3 = New adodb.Recordset
rs3.Open sSQL, cn, adOpenKeyset, adLockOptimistic
Set DataGrid2.DataSource = rs3

and it works ok.

i think the problem rather in filtering data than displaying them...?

 

[BobRodes]

<but the code is not working

The problem is that you don't understand filters. What you THINK you're doing is applying a filter, going and getting the records that fit that filter, and then conscientiously removing the filter until the next use, leaving yourself the records that you went and fetched when you last applied the filter. What you're ACTUALLY doing is restricting the records that you can see in the recordset to those which fit the filter, making an irrelevent trip to fetch the same records you fetched in the first place, and then removing the restriction that you just placed, all before returning control to the user. In other words, you accomplish precisely nothing.

You think that a filter defines the criteria for the next fetch of records from the database, and it doesn't. It only restricts the ones you can see, and is effective immediately on your existing recordset. So, stop refreshing your grid, and stop setting your filter to none.

Of course, a perusal of the code sample I gave you would reveal all of this. If you will evaluate the code in the click event of the check box, you'll notice that it does nothing to the grid, and nothing to the recordset but set its filter property. All the rest of the code is to determine what the filter is.

So, might I suggest that you go to the trouble of examining the code I posted in more detail? You really might save yourself a bit of time.

Bob

 

[BobRodes]

<i think the problem rather in filtering data than displaying them...?

You're on the right track....

 

[jozino01]

thanks to everybody; i got it rigth this time (at least it does what i want


If chTS.Value = 1 Then
rs3.Filter = "Type LIKE 'TS*'"
Set DataGrid2.DataSource = rs3
End If

If chAS.Value = 1 Then
rs3.Filter = "Type LIKE 'AS*'"
Set DataGrid2.DataSource = rs3
End If

 

[jozino01]

and here is the complete code:

If chTS.Value = 1 And chAS.Value = 1 Then

If chE.Value = 1 And chD.Value = 0 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSE*' OR Type LIKE 'ASE*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSD*' OR Type LIKE 'ASD*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSV*' OR Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSE*' OR Type LIKE 'TSD*' OR Type LIKE 'ASE*' OR Type LIKE 'ASD*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSD*' OR Type LIKE 'TSV*' OR Type LIKE 'ASD*' OR Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSE*' OR Type LIKE 'TSV*' OR Type LIKE 'ASE*' OR Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TS*' OR Type LIKE 'AS*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 0 Then
MsgBox "Select model first."
Exit Sub
End If

Exit Sub
End If

If chTS.Value = 1 Then

If chE.Value = 1 And chD.Value = 0 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSE*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSD*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'TSE*' OR Type LIKE 'TSD*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSD*' OR Type LIKE 'TSV*'"
End If

If chE.Value = 1 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TSE*' OR Type LIKE 'TSV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'TS*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 0 Then
MsgBox "Select model first."
Exit Sub
End If

Set DataGrid2.DataSource = rs3

Exit Sub
End If

If chAS.Value = 1 Then

If chE.Value = 1 And chD.Value = 0 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'ASE*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'ASD*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 0 Then
rs3.Filter = "Type LIKE 'ASE*' OR Type LIKE 'ASD*'"
End If

If chE.Value = 0 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'ASD*' OR Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 0 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'ASE*' OR Type LIKE 'ASV*'"
End If

If chE.Value = 1 And chD.Value = 1 And chV.Value = 1 Then
rs3.Filter = "Type LIKE 'AS*'"
End If

If chE.Value = 0 And chD.Value = 0 And chV.Value = 0 Then
MsgBox "Select model first."
Exit Sub
End If

Set DataGrid2.DataSource = rs3

Exit Sub
End If

 

[BobRodes]

jozhinho, I'm glad you got it working--after all that's the first step in software development--but you could do it with a lot less code. This code is about 1/3 the size of yours and does pretty much the same thing:

Private Sub chkCrit_Click(Index As Integer)
Dim i As Integer
Dim strFilter As String
strFilter = "Type LIKE '["
For i = 0 To 1
    If chkCrit(i) = vbChecked Then
        Select Case i
            Case 0
                strFilter = strFilter & "T"
            Case 1
                strFilter = strFilter & IIf(Right(strFilter, 1) <> "[", ",", "") & "A"
        End Select
    End If
Next i
strFilter = strFilter & "]S["
For i = 2 To 4
    If chkCrit(i) = vbChecked Then
        Select Case i
            Case 2
                strFilter = strFilter & "E"
            Case 3
                strFilter = strFilter & IIf(Right(strFilter, 1) <> "[", ",", "") & "D"
            Case 4
                strFilter = strFilter & IIf(Right(strFilter, 1) <> "[", ",", "") & "V"
        End Select
    End If
Next i
strFilter = strFilter & "]*'"
Label1 = strFilter
End Sub

The code assumes a form with 5 checkboxes in a control array called chkCrit, and one label called Label1. It doesn't evaluate the boundary conditions, and it doesn't keep resetting the DataSource (you only need to do that once in Form_Load). It also doesn't plug the logic into a Recordset Filter. The main things that it does are (1) streamline the way that your WHERE clause works (the clause [tt]"Type LIKE 'TSE*' OR Type LIKE 'TSD*' OR Type LIKE 'ASE*' OR Type LIKE 'ASD*'"[/tt] can also be expressed as [tt]"Type LIKE '[T,A]S[E,D]*'[/tt] which is what I have done) and (2) eliminate most of the decision tree by building a single string one block at a time rather than choosing from all of the possible permutations of the string.

If you'd like to try the code out and have questions as to how it works I'll bd glad to answer them.

HTH

Bob

 

[jozino01]

thanks bob; i knew there is a better way