Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

Using list boxes for Criteria

How to change SQL on query Dynamically when using Multi select Listbox by bhoran
Posted: 11 Jan 04 (Edited 12 Jan 04)

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!"

    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!"
'building of 1st Sql and filter statement
sSql1 = "Account In ("""

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

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) & """, """

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
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.

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.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close