Create A Filtered Recordset From An Open Recordset
Sometimes it is useful to use the same recordset in a module but dynamically filter it based on a multi-select list box. Filtering a recordset based on a multi-select list box can be done in a number of ways:
(1) Set the filter property in a form or report
(2) Open the recordset with a SQL statement that includes a WHERE clause
(3) Include a function that returns a SINGLE value in the "Criteria" section of the graphic query builder
(4)
Build a table of filtering criteria and use the table in an Inner JOIN
(5) Open a secondary recordset based on the primary recordset using the recordset.filter property
This example illustrates option 5.
Program Code
Option Compare Database
Option Explicit
' ***********************************************************************
' Create A Filtered Recordset FROM An Open Recordset
' ***********************************************************************
Public Function TestFilter()
Dim db As Database
Dim recIn As Recordset
Dim recFiltered As Recordset
Dim intRecCount As Integer
intRecCount = 0
' ***********************************************************************
' Open The Primary Recordset
' ***********************************************************************
Set db = CurrentDb()
Set recIn = db.OpenRecordset("qryProducts")
If recIn.EOF Then
MsgBox ("No Input Records")
recIn.Close
Set recIn = Nothing
Set db = Nothing
Exit Function
End If
recIn.MoveLast
recIn.MoveFirst
MsgBox ("Primary Record Count = " & recIn.RecordCount)
' ***********************************************************************
' Set A Filter For The Primary Recordset
' ***********************************************************************
recIn.Filter = "[Category 1] IN('AA','CP')"
' ***********************************************************************
' Open A Second Recordset By Filtering the First Recordset
' ***********************************************************************
Set recFiltered = recIn.OpenRecordset
recFiltered.MoveLast
recFiltered.MoveFirst
MsgBox ("Filtered Record Count = " & recFiltered.RecordCount)
' ***********************************************************************
' Loop Through and Print Up To 5 Records
' ***********************************************************************
Do
Debug.Print recFiltered![Category 1]
intRecCount = intRecCount + 1
recFiltered.MoveNext
Loop Until recFiltered.EOF Or intRecCount = 5
recFiltered.Close
recIn.Close
Set recFiltered = Nothing
Set recIn = Nothing
Set db = Nothing
End Function