Setting RecordSource from foundset (1 Viewer)

[rbsterli]

Hello, I am trying to use a recordset as a RecordSource for my report. I am doing it by creating a query "on the fly" then deleting it after use. Can anyone tell my from my poor, but working example of code how to do this without creating and deleting these temp queries, as I am using similar methods elsewhere throughout my database. Thanks in Advance!

strSQL = "Select * from RealReferralMemoSource where [Div_Reg_Name]="

For Each varItem In ctl.ItemsSelected
If bolSecondTime = False Then
strSQL = strSQL & Chr(34) & ctl.ItemData(varItem) & Chr(34)
bolSecondTime = True
Else
strSQL = strSQL & " OR [Div_Reg_Name]=" & Chr(34) & ctl.ItemData(varItem) & Chr(34)
End If
Next varItem
Set dbs = CurrentDb

Dim qdf As QueryDef
Set qdf = dbs.CreateQueryDef("tmpQuery2", strSQL)

I then set the recordsource of the report to "tmpQuery2"

 

[BarkerD]

Can you not use the SQL statement itself as the Recordsource for the Report?

I've never tried this, but I think it should work. It would also eliminate alot of overhead from the temporary objects.

 

[Travis]

BarkerID is correct, you can use the stSQL as the recordsource. But how do you get it there?

You could add your code to create the stSQL to the Report_Open Event and then set the Reports RecordSource = stSQL

or

You could create a Public Function to return the stSQL

on the Report_Open Event add this code:
Private Sub Report_Open ()
Me.RecordSource = GetRecordSource()

end Sub

Add this to a Module:

Public Function GetRecordSource() as String
strSQL = "Select * from RealReferralMemoSource where [Div_Reg_Name]="

For Each varItem In ctl.ItemsSelected
If bolSecondTime = False Then
strSQL = strSQL & Chr(34) & ctl.ItemData(varItem) & Chr(34)
bolSecondTime = True
Else
strSQL = strSQL & " OR [Div_Reg_Name]=" & Chr(34) & ctl.ItemData(varItem) & Chr(34)
End If
Next varItem

End Function

make sure you reference the form