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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeTooleCommented:
The property you want is RecordsetClone - i.e.

Set newRS = Me.RecordsetClone
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
flavoCommented:
err.. That should have been

  With rs
        .Filter = MyFilter
        Set rs = .OpenRecordset
    End With
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Morning :-)
SweetsGreenCommented:
there is no FilterOn

newRS.Filter = MyFilter '''turns on a filter
newRS.Filter = 0 ''turns off the filter
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.