Passing SQL String to a Report

[stukanewss]

In a form I have a SQL string that is dynamically created, and I'm trying to pass that to the record source of a report. The procedure to create the string resides in a private event button click procedure. I have set up a public variable in another module and I can not pass that value to the report recordsource property. Any suggestions?

Here is the code for the string creation. The string creation part of this works:

Private Sub Pending_Click()

Dim icounter As Integer
Dim squery As String
Dim sselected As String

For icounter = 0 To List0.ListCount - 1
If List0.Selected(icounter) Then
sselected = sselected & IIf(Len(sselected) = 0, "", ",&quot & Chr(39) & List0.Column(0, icounter) & Chr(39)
End If

Next icounter

MsgBox (sselected)

If Len(sselected) > 0 Then

squery = "SELECT Local Opty.Account, Local Opty.Author, Local Opty.Title, Local Opty.Edition, Local Opty.Units, Local Opty.Revenue, Local Opty.Status, Local Opty.[Decision Date], Local Opty.Discipline, Local Opty.Course, Local Opty.Type, Local Opty.[Sales Rep], Local Opty.[Semester of Use], Local Opty.ISBN, Count(*) AS [Count Of Opty], Count(*) AS [Count of SalesRep] FROM Opty GROUP BY Local Opty.Account, Local Opty.Author, Local Opty.Title, Local Opty.Edition, Local Opty.Units, Local Opty.Revenue, Local Opty.Status, Local Opty.[Decision Date], Local Opty.Discipline, Local Opty.Course, Local Opty.Type, Local Opty.[Sales Rep], Local Opty.[Semester of Use], Local Opty.ISBN HAVING (((Local Opty.Status)='Pending - High' Or (Local Opty.Status)='Pending - Low' Or (Local Opty.Status)='Pending - Medium') AND ((Local Opty.[Sales Rep]) In ( " & sselected & &quot));"

Else

squery = "SELECT Local Opty.Account, Local Opty.Author, Local Opty.Title, Local Opty.Edition, Local Opty.Units, Local Opty.Revenue, Local Opty.Status, Local Opty.[Decision Date], Local Opty.Discipline, Local Opty.Course, Local Opty.Type, Local Opty.[Sales Rep], Local Opty.[Semester of Use], Local Opty.ISBN, Count(*) AS [Count Of Opty], Count(*) AS [Count of SalesRep] FROM Opty GROUP BY Local Opty.Account, Local Opty.Author, Local Opty.Title, Local Opty.Edition, Local Opty.Units, Local Opty.Revenue, Local Opty.Status, Local Opty.[Decision Date], Local Opty.Discipline, Local Opty.Course, Local Opty.Type, Local Opty.[Sales Rep], Local Opty.[Semester of Use], Local Opty.ISBN HAVING (((Local Opty.Status)='Pending - High' Or (Local Opty.Status)='Pending - Low' Or (Local Opty.Status)='Pending - Medium');"

End If


On Error Resume Next

rcdsrce = squery


DoCmd.OpenReport "Rep Pending", acViewPreview

End Sub

Here is the Public variable I have created in a separate module:

Option Compare Database

Dim rcdsrce As String


Here is the On Open Event procedure for the report:

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = rcdsrce

End Sub

 

[WildHare]

You might want to check out the QUERYDEF collection - it's probably what you want. You can assign this string to a 'querydef', creating a query object. Then your report can refer to the query object.

But even that seems like an awful lot of junk to go through for what are in effect, two nearly identical queries. Why not just create one explicit query, with the criteria and a reference to the multi-select list box as well, that plugs in the values of the selected sales reps id's if they exist, or leaves it as the famous "Like *" if there aren't any selected?

Jim Hare
"Remember, you're unique - just like everyone else"

http://www.jmhare.com

 

[cmmrfrds]

Dim rcdsrce As String
should be a public variable otherwise the scope is limited to the module.
Public rcdsrce As String

Also you could create a function in the standard module where the variable is located.

Public Function querystring() as string
querystring = rcdsrce
End Function

In OnOpen of Report
Me.Recordsource = querystring()