Results 1 to 3 of 3
  1. #1
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79

    Filter on Previous Filter

    I'd like to investigate if it's possible to speed up my operations. I'd like to:


    1. Create a Main Recordset as a rough filter
    2. Use the Main Recordset (in step 1) as source to make a finer filter. This can be repeated many times by a For Next loop

    So instead of searching from a complete table every time in my For Next loop, I'd like to search from the Main Recordset instead.

    My first thought was to use a Query in Navigation Pane as my Main Recordset. Query = "QMain" in eaxmple beneath. However, I am afraid that QMain is updated every time in my For Loop beneath. So I believe I won't gain any solution time.

    Code:
    Sub UseQueryInNavigationPane()
        
        Dim li as Long
        Dim sSQL_Fine As String
        Dim sSQL_Main As String
        Dim rst As ADODB.Recordset
        
        'Change SQL to a Query stored in Navigation Pane
        sSQL_Main = "SELECT * FROM TMyTable WHERE ..."    'SQL for my Main Recordset
        CurrentDb.QueryDefs("QMain").SQL = sSQL_Main
        
        'Fine Filter on "QMain"
        Set rst = New ADODB.Recordset
        For li = 0 To 1000
            sSQL_Fine = "SELECT * FROM QMain WHERE ..."    'SQL for my Fiine Recordset. I'm afraid QMain is updated every time in my For Next loop.
            With rst
                .ActiveConnection = CurrentProject.Connection
                .CursorType = adOpenStatic
                .Source = sSQL_Fine
                .Open
                
                'Do something
                '...
    
                .Close
            End With
        Next li
        Set rst = Nothing
    End Sub
    My 2nd thought was to use ADO recordset directly in VBA. But I have no idea how to write my 2nd SQL-code for the finer recordset beneath. I need to involve rstMain somewhere in my SQL-code.

    Code:
    Sub AllInVBA()
        
        Dim li as Long
        Dim sSQL_Fine As String
        Dim sSQL_Main As String
        Dim rstFine As ADODB.Recordset
        Dim rstMain As ADODB.Recordset
    
        Set rstMain = New ADODB.Recordset
        Set rstFine = New ADODB.Recordset
        
        'Main Filter
        sSQL_Main = "SELECT * FROM TMyTable WHERE ..."    'SQL for my Main Recordset
        With rstMain
            .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenStatic
            .Source = sSQL_Main
            .Open
        end With  
    
        'Fine Filter
        For li = 0 To 1000
            sSQL_Fine = ?            'SQL for my Fine Recordset. I have no idea how to write this involving rstMain
            With rstFine
                .ActiveConnection = CurrentProject.Connection
                .CursorType = adOpenStatic
                .Source = sSQL_Fine
                .Open
                
                'Do something
                '...
    
                .Close
            End With
        Next li
    
        rstMain.Close
        Set rstMain = Nothing
        Set rstFine = Nothing
    End Sub
    If someone has an idea how to use Filter on Filter, I'd appreciate your comments.

    \Joakim

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure you understand the way filters need to be applied because I can't make sense of what you're doing. If you have a query that returns all the records you need, why are you not creating a filter then applying it? If the filter has to change, I presume you know how that works; i.e. you re-create it and reapply it - not attempt to apply a filter to a single filtered recordset. This presumes the record set is not huge and that repeatedly modifying and applying it doesn't slow things down. Sometimes it is more efficient to reconstruct the sql statement for a recordset and rebuild it.

    That being said, I'd use DAO, not ADO but that's based on a lack of information which is probably related to the "do something" part. Maybe you need ADO for that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As Micron mentioned, I also don't follow the rough filter, then a fine filter.
    Perhaps if you told us in plain English WHAT you are trying to accomplish so readers can understand the situation, they may offer options and/or more focused responses.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-18-2016, 09:13 PM
  2. Newbie: How to filter depending on previous selected.
    By Zeeker in forum Database Design
    Replies: 1
    Last Post: 01-28-2016, 12:34 PM
  3. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  4. Replies: 9
    Last Post: 10-29-2013, 02:13 PM
  5. Replies: 3
    Last Post: 10-22-2010, 06:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums