Recordset.clone help

Posted on 2006-04-24
Last Modified: 2008-01-16
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
Question by:tim_cs
    LVL 27

    Assisted Solution

    The property you want is RecordsetClone - i.e.

    Set newRS = Me.RecordsetClone
    LVL 34

    Assisted Solution

    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

    LVL 34

    Expert Comment

    err.. That should have been

      With rs
            .Filter = MyFilter
            Set rs = .OpenRecordset
        End With
    LVL 44

    Accepted Solution

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

    Expert Comment

    by:Leigh Purvis
    Morning :-)
    LVL 7

    Expert Comment

    there is no FilterOn

    newRS.Filter = MyFilter '''turns on a filter
    newRS.Filter = 0 ''turns off the filter

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now