Microsoft Access Office VB VBA Help and Examples

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 "
   If stDocCriteria <> "" Then
  stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
  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.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.