I had a situation where I needed to use criteria from 2 multi-select listboxes for a report and also for the subreport.
I could use the multi-select listboxes results as a filter for the the main report.
The problem I had was that the sub report was aggregating data in a different way to the main report therefore I needed to use the same criteria used for the Main report in the query that is the source of the subreport.
Thanks to dhookum and Make it So I used the following code to facilitate this.
To dynmically change the SQL of the source query used by the subreport I needed to use the following function saved in a new module called "modQueryFunctions":
Function ChangeSQL(pstrQuery As String, pstrSQL As String) As String Dim db As DAO.Database Dim qd As DAO.QueryDef Set db = CurrentDb Set qd = db.QueryDefs(pstrQuery) ChangeSQL = qd.SQL 'return old sql qd.SQL = pstrSQL 'set new sql Set qd = Nothing Set db = Nothing End Function
You also need to create the Filter and Sql statements dynamically so you can use the listbox results as a filter on the main report and as the SQL for the subreport source, as we want these to change each time the repotr is opened.
I used the following code:
Private Sub Command41_Click()
Dim RepTo As String Dim ctlList1 Dim ctlList2 Dim sSql1 As String Dim sSql2 As String Dim strSQL As String Dim strOldSQL As String Set ctlList1 = Me.lstACCOUNT Set ctlList2 = Me.lstBrand
' if statements to ensure both listboxes have selections made. If ctlList1.ItemsSelected.Count = 0 Then Msgbox "No Accounts have been selected," & (Chr(13)) & (Chr(13)) & _ "Please select at least one Account from the list", vbExclamation, _ "Selection Error!"
Else If ctlList2.ItemsSelected.Count = 0 Then Msgbox "No Brands have been selected," & (Chr(13)) & (Chr(13)) & _ "Please select at least one Brand from the list", vbExclamation, _ "Selection Error!"
Else 'building of 1st Sql and filter statement sSql1 = "Account In ("""
For Each Lmnt In ctlList1.ItemsSelected sSql1 = sSql1 & ctlList1.ItemData(Lmnt) & """, """ Next
sSql1 = Left(sSql1, Len(sSql1) - 3) & ")" 'Remove Last comma and single quote and add closing bracket.
'building of 2nd Sql and filter statement sSql2 = "Brand In ("""
For Each Lmnt In ctlList2.ItemsSelected sSql2 = sSql2 & ctlList2.ItemData(Lmnt) & """, """ Next
sSql2 = Left(sSql2, Len(sSql2) - 3) & ")"
'Building actual SQL statement for change using sSql1 & sSql2. strSQL = "SELECT * " strSQL = strSQL & "FROM [MthlyMktgValsTotalRptg]" strSQL = strSQL & "WHERE " & sSql1 & "And " & sSql2 'change the sql property of the query "MthlyMktgValsTotalRptg1" strOldSQL = ChangeSQL("MthlyMktgValsTotalRptg1", strSQL) 'your query will now have a new sql value
'open report using the where clause as ssql1 & sSql2 RepTo = "FCASTENTRYMTHLY_MKTG" DoCmd.OpenReport RepTo, acViewPreview, , sSql2 & " And " & " " & sSql1 End If End If End Sub
As you can see the statements are built in sSql1 & sSql2 then utilised in the code to change the sql in the query (source for subreport) and also used as the filter for the main report.
N.B You also have to switch DAO Objects on. When in a module choose refrences from the tools menu then scroll down and select Microsoft DAO Objects.
If using User-Level security, don't forget that the Users need to have permission to modify the query that is having the SQL changed.