ADO filter ‘fails’ using array of bookmarks

[Glasgow]

I have a grid bound to a recordset. I am using the recordset’s ADO filter property in an attempt to restrict the visible records. However I am using an array of bookmarks to assign the filter. When I do so, the RecordCount property of the recordset changes (as I would expect) but the number of records shown in the grid is unchanged. If I use a criteria string to assign the filter, everything operates as I would expect – i.e. number of records displayed changes also.

Does anyone have any idea as to why this should behave in this way and how I might get around it?

 

[LostInCode]

Sorry, I think that is not the purpose of filtering with array of bookmarks.

A filter never effect persisted recordset when using array of bookmarks. It is only effected when you specify specific filter.
And this I think is good so. Filter method has several purposes - maybe 5 or 6, and only one purpose really hides filtered records.

 

[LostInCode]

>hides filtered records

I mean when using with bound grid or whatever.

 

[Glasgow]

Thanks. I had noticed that the documentation suggests that persisted recordsets are treated differently and perhaps my interpretation of a 'persisted' recordset is incorrect. Is a persisted recordset not one which has been written to disk using the .Save method? This does not apply here.

Or is a persisted recordset one that has been retrieved from a disk source of some sort - e.g. XML, database, spreadsheet and a non-persisted one is simply one that resides only in memory?

 

[LostInCode]

>I think that is not the purpose of filtering with array of bookmarks

I understood the problem because of my own experience to have to do with bound control such as the datagrid.
So I understood this is what you mean by not seeing the records.

The recordset shows in code only filtered records alright.

But the "recordset" for bound control, and saved recordsets, is persited, it is how I have learned to understand it.


I find this a good thing because if you use a datagrid and still want to view records that are in conflict with saved records during update, or want to see only the records really saved, or want or see records waiting to be saved, or in the case of filter on array of bookmarks, want to make a tempoary sub group of a recordset to work on, or even to create a new local recordset out of it, then the data in the grid is not effected.

 

[Glasgow]

I confess I'm a bit lost but thanks for 'persisting' with the problem.

What I really wanted to do was to take the selected rows from one grid (data grid), transfer to a TrueDbGrid then print only the selected records.

I have found a workaround using the SelBookmarks collection and a Selector parameter that can be passed to the print mechanism to print only selected records.

Thanks again.

 

[LostInCode]

But the recordset should still show only the filtered records -just not the grid
So, you can easily create recordset from this.
Make new recordset objekt, append fields by looping through the original fields collection, then just loop through the filtered data and add that to the new recordset - it is a very small routine and most easy to work with.

 

[Glasgow]

I had considered that approach but it seemed a bit like a hammer to crack a nut. In the end, my 'workaround' is probably a better solution than what I was originally setting out for so in some ways it's probably just as well I was forced to find another solution.

Thanks again.

 

[LostInCode]

Good. But a rs copy is easy to work with.
I post some code so you can see:

Public Function CopyRS(rstOrig As ADODB.Recordset) As ADODB.Recordset
    Dim rstCopy  As ADODB.Recordset 'Not updatable to db - no connection
    Dim fld As ADODB.Field
    
    Set rstCopy = New ADODB.Recordset
    
    With rstOrig
        .MoveFirst
        For Each fld In .Fields
            rstCopy.Fields.Append fld.Name, fld.Type, fld.DefinedSize, fld.Attributes
        Next fld
        
        rstCopy.Open
        Do Until .EOF
            rstCopy.AddNew
            
            For Each fld In .Fields
                rstCopy.Fields(fld.Name).Value = fld.Value
            Next fld
            rstCopy.Update
            .MoveNext
        Loop
    End With
    rstCopy.MoveFirst
    
End Function

Just do it like so:

Dim RstCopy As ADODB.recordset

Set RstCopy = CopyRS(MyOriginalRs)

The copy is lokal and can not update to db direct. You will need to update like such

conn.Execute "Update...."

or find the same record in the original and change values there.

When you be done with it, just use RstCopy.close and set to nothing.