Recordset.clone help

I'm doing a clone of a recordset using set newRS = me.recordset.clone.  I'm then setting the filter for the cloned recordset which works fine.  The problem occurs when I try to turn the filter on.  I get an error message saying "Object doesn't support this property or method".  Why am I able to set a filter for the cloned recordset but not turn the filter on?  Is this possible and I'm just doing something wrong?  



Set newRS = Me.Recordset.Clone
    If newRS.RecordCount > 0 Then
        MyBookMark = newRS.Bookmark

        newRS.Filter = MyFilter
        newRS.FilterOn = True   'Error thrown here.
    End If
LVL 15
tim_csAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
You need to set the filter in a recordset *before* opening a subsequent clone of it.
For example

    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
   
    Set rst = Me.Recordset.Clone
   
    Debug.Print rst.RecordCount
    rst.Filter = MyFilter
   
    Set rst2 = rst.OpenRecordset
    rst2.Print .RecordCount


Should show a filtered recordcount for the second recordset (because the filter was set on the first one before opening).
You're almost always as well off opening a new recordset anyway - but based on a restricted SQL statement (i.e. with a WHERE clause).
0
 
MikeTooleCommented:
The property you want is RecordsetClone - i.e.

Set newRS = Me.RecordsetClone
0
 
flavoCommented:
There is no FilterOn method for a recordset (DAO or ADO)

Example using DAO:

    Dim rs As DAO.Recordset
   
    Set rs = Me.RecordsetClone
       
    With rs
        .Filter = MyFilter
        Set rs = rs.OpenRecordset
    End With

Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
flavoCommented:
err.. That should have been

  With rs
        .Filter = MyFilter
        Set rs = .OpenRecordset
    End With
0
 
Leigh PurvisDatabase DeveloperCommented:
Morning :-)
0
 
SweetsGreenCommented:
there is no FilterOn

newRS.Filter = MyFilter '''turns on a filter
newRS.Filter = 0 ''turns off the filter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.