How to change SQL on query Dynamically when using Multi select Listbox

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 [color green]'return old sql[/color]
qd.SQL = pstrSQL [color green]'set new sql[/color]
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

[color green]' if statements to ensure both listboxes have selections made.[/color]
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
[color green]'building of 1st Sql and filter statement[/color]
sSql1 = "Account In ("""

For Each Lmnt In ctlList1.ItemsSelected
sSql1 = sSql1 & ctlList1.ItemData(Lmnt) & """, """
Next

sSql1 = Left(sSql1, Len(sSql1) - 3) & ")" [color green]'Remove Last comma and single quote and add closing bracket.[/color]

[color Green]'building of 2nd Sql and filter statement [/color]
sSql2 = "Brand In ("""

For Each Lmnt In ctlList2.ItemsSelected
sSql2 = sSql2 & ctlList2.ItemData(Lmnt) & """, """
Next

sSql2 = Left(sSql2, Len(sSql2) - 3) & ")"

[color green]'Building actual SQL statement for change using sSql1 & sSql2. [/color]
strSQL = "SELECT * "
strSQL = strSQL & "FROM [MthlyMktgValsTotalRptg]"
strSQL = strSQL & "WHERE " & sSql1 & "And " & sSql2
[color green]'change the sql property of the query "MthlyMktgValsTotalRptg1"[/color]
strOldSQL = ChangeSQL("MthlyMktgValsTotalRptg1", strSQL)
[color green]'your query will now have a new sql value[/color]

[color green]'open report using the where clause as ssql1 & sSql2[/color]
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.