Report Menu - Multi select Listbox Filter?

M

macrojunkie

I have a report and I want to set up a menu so users can filter using
a multi select list box. I have seen some sites that show examples of
how to do this, but I have never really learned much about list boxes
and how to use them. This is my current set up:

I have a report called rptByStatusLocation. It has all of the main
record summary information for this application. This has a query
called qryRecordByLocationReport.

I set a form to this as well and created a list box on the form called
LstRecordStatus.

The rowsource for this is the same field in the query that is in the
report for RecordStatus (txtRecordStatus)

This is the code I have on the form:

Code:
Private Sub cmdReportByLocation_Click()
On Error GoTo Err_cmdReportByLocation_Click

Dim stDocName As String

stDocName = "rptByStatusLocation"
DoCmd.OpenReport stDocName, acPreview, , GetCriteria()

Exit_cmdReportByLocation_Click:
Exit Sub

Err_cmdReportByLocation_Click:
MsgBox Err.Description
Resume Exit_cmdReportByLocation_Click

End Sub

Private Function GetCriteria() As String
'Multi-Select Listbox
Dim ctlList
Set ctlList = Me.LstRecordStatus

If Me.LstRecordStatus.ItemsSelected.Count = 0 Then
'do nothing
Else
strWhere = strWhere & " AND Product IN ("

For Each Lmnt In ctlList.ItemsSelected

strWhere = strWhere & "'" & ctlList.ItemData(Lmnt) &
"',"

Next
strWhere = strWhere & ")"
End If
End Function

When I run the form and choose 1 of the options, but not both, I still
get both (only 2 options are there right now, because they are the
only ones used in any of the records in the application right now).

I am not sure what I am not doing right, because I am so new to list
boxes. Any help is greatly appreciated. Eventually I have 2 more
list boxes I want to add to this form so a user can run 3 filters
choosing none or all of each of the 3 sets of filters.

Any help is greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top