Several steps are needed:
1) create a query that runs without selected values - example "select
CustomerName from tblCustomers;"
Save it. Copy this query and name the copy with a z in front of the
original name, like "zqryCustomers"
2) On your form with the listbox, you have a command button that executes
the code to open the report.
Create some variables, like strCatList, which will hold the list, Myqry,
which holds the saved query name, and a variable myStr, which holds the
selected fieldname.
All of the following code is in the click event, with the exception of the
called function, which is in a global module.
strCatList = BuildWhereCondition("lstAll") 'lstAll name of listbox
and fill it by calling the BuildWhereCondition function:
*******************************
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control
Set ctl = Forms!frmReports!(strControl) 'Name of your form with listbox
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("
With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) &
"', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
BuildWhereCondition = strWhereLst
Set ctl = Nothing
End Function
**********************
'Get the template query - this gives us a "clean" sql to work with, as any
previous runs changes the original
stored query....
myqryZ = "z" & Myqry
strsql = CurrentDb.QueryDefs(myqryZ).sql
3) The strCatList now may hold your listbox selections, or be blank if none
selected...
Need to Add Items if any selected. If none selected, all included
See if any criteria was selected:
If Len(strCatLst) > 0 Then
strCatLst = " WHERE " & Mystr & strCatLst & ";"
'this variable should read something like "WHERE CompanyName IN
('Macys','BestBuy');"
End If
4) Update the query with the criteria
strsql = Replace(strsql, ";", strCatLst)
CurrentDb.QueryDefs(Myqry).sql = strsql
5) Now, finally, run your report:
DoCmd.OpenReport "YourReportName", acPreview
'Note that the record source for the report should be your saved query.
If you have any problems, repost...
Damon
