Filter a Report based on List Box
This sample shows how to open a report
based on a multiple selection in a list box. For example you may have a list of
makes of car and a report that shows various details for each make of car. You
want to allow the users to select a range of cars and pull up the report
according to that selection.
In your report you must have a unique field
that identifies each type of car. This is what we will use for our filter. This
will typically be the field upon which our page breaks are built in the report.
But this is not necessary.
In our example we are using the CarID field. Our
report is called rpt_CarSales and we have a query called qry_CarIDs which lists
all type of cars with their CarID.
Create a new form. On it place a listbox
called ListFilter. Set its rowsource equal to the query that contains the fields
you want to see in it. In our example we are setting it to qry_CarIDs. Make sure
you have set it's multiselect property to simple if you want the users to pick
more than one choice. Go into
the code view of the form and paste the following function.
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & "[CarID] = " &
ListFilter.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function |
Then create a button on your form and build an event for it and in code type the
following.
Private Sub
ButtonOpen_Click()
DoCmd.OpenReport "rpt_CarSales", acPreview, ,
GetCriteria()
End Sub |
When the button is clicked (our button was called ButtonOpen) it ill open the
report showing only those CarID's selected in the list box. If none are chosen
it will return all.
| 