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
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).
The property you want is RecordsetClone - i.e.

Set newRS = Me.RecordsetClone
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

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.

err.. That should have been

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

newRS.Filter = MyFilter '''turns on a filter
newRS.Filter = 0 ''turns off the filter
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.