Filter Property Example (MDB)

The following example prompts the user to enter the name of a country on which to filter, and then sets the Filter property of a dynaset-type Recordset object. For example, if the user enters "Italy", the filtered recordset will contain only those records in which the ShipCountry field contains the string "Italy".

Note that you first create a dynaset-type Recordset object and set its Filter property, then open a second dynaset-type Recordset object based on the first. Setting the Filter property of the first Recordset object doesn't actually affect the records that it contains, so you need to create a second Recordset object in order to see the effects of the filter.

Sub SetRecordsetFilter()
    Dim dbs As Database, strInput As String
    Dim rstOrders As Recordset, rstFiltered As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Prompt for value on which to filter.
    strInput = InputBox("Enter name of country on "
        & "which to filter.")
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Set filter condition.
    rstOrders.Filter = "ShipCountry = '" & strInput & "'"
    ' Create filtered dynaset-type Recordset object.
    Set rstFiltered = rstOrders.OpenRecordset
    rstOrders.Close
    rstFiltered.Close
    Set dbs = Nothing
End Sub

Tip   In most situations, it's more efficient to create the second Recordset object with the desired conditions in one step. When you know what data you want to select, it's generally more efficient to create a recordset with an SQL statement. The next example shows how you can create just one recordset and obtain the same results as in the preceding example:

Sub CreateRecordsetWithSQL()
    Dim dbs As Database, rst As Recordset
    Dim strInput As String

    Set dbs = CurrentDb
    strInput = InputBox("Enter name of country on which to filter.")
    Set rst = dbs.OpenRecordset("SELECT * FROM " _
        & "Orders WHERE ShipCountry = '" & strInput _
        & "';")
    rst.MoveLast
    MsgBox "Recordset contains " & rst.RecordCount & " records."
    rst.Close
    Set dbs = Nothing
End Sub