Today, I thought I’d touch upon how you can filter an existing recordset.

I didn’t know how to approach this subject myself back in 2013 and Dirk Goldgar and Henry Habermacher were kind enough to teach me.

Recently, I saw a question on UtterAccess pertaining to this and thought it was a worthwhile subject to post on my blog to help other developers with.

The Concept

So what am I talking about exactly?

Perhaps you have a form of contacts listed that the user has already filtered down and you can easily grab that recordset from the form, but you wanted to only get the contacts from a specific city, or gender, or …

Yes, you could identify the existing filters, and new filters and create a new SQL statement to pull the info from, but you can also simply filter the form’s recordset directly.

Below are 2 variations of the same basic technique which illustrate how this can be done.

In the examples below I’m filtering only those which records are Active (Active = True), but the filter could be anything you’d like.

Method 1

    Dim rs                    As DAO.Recordset
    Dim rsFiltered            As DAO.Recordset

    Set rs = Me.RecordsetClone
    rs.Filter = "[Active] = True"
    Set rsFiltered = rs.OpenRecordset
    With rsFiltered
        If .RecordCount <> 0 Then
            .MoveFirst
            Do While Not .EOF
                ' Do something with each filtered record
                .MoveNext
            Loop
        End If
    End With

    On Error Resume Next
    If Not rsFiltered Is Nothing Then
        rsFiltered.Close
        Set rsFiltered = Nothing
    End If
    If Not rs Is Nothing Then Set rs = Nothing

Method 2

    Dim rsFiltered            As DAO.Recordset

    With Me.Recordset
        .Filter = "[Active] = True"
        Set rsFiltered = .OpenRecordset
    End With

    With rsFiltered
        If .RecordCount <> 0 Then
            .MoveFirst
            Do Until .EOF
                ' Do something with each filtered record
                .MoveNext
            Loop
        End If
    End With

On Error Resume Next
    If Not rsFiltered Is Nothing Then
        rsFiltered.Close
        Set rsFiltered = Nothing
    End If

As you can see, it truly isn’t hard, once you are aware of the basic principle and then it’s like working with any other recordset and looping mechanism.

A Few Resources on the Subject

Access Recordset Looping

One of the most common things that all Access developers do is loop through recordsets to read them, edit them, … I thought I’d give a few code samples for those of you that are just starting off.

Recordset object (DAO)

Office developer client VBA reference