Unanswered: Changing recordset with Querydefs with multiple instances?
HI. I;'ve got a report where the On Open event is:
Private Sub Report_Open(Cancel As Integer)
Dim qdf As DAO.QueryDef
Dim rstReport As DAO.Recordset
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("Temp_PaymentsFeesUnion")
qdf.SQL = "EXEC dbo.sp_PaymentsFeesUnion " & Forms![Rent Monitor 10].ID
qdf.ReturnsRecords = True
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
Me.RecordSource = "Temp_PaymentsFeesUnion"
Set qdf = Nothing
Set db = Nothing
In order to to that you have to change the Filter property of the new instance of the report. Here is an exerpt from an application I wrote:
Function OpenAClient(ReportCaption As String, ReportFilter As String)
'Purpose: Open an independent instance of Report New_Delivery_Schedule.
Dim rpt As Report
'Open a new instance, show it, and set various properties.
Set rpt = New Report_New_Delivery_Schedule
rpt.Filter = ReportFilter
rpt.FilterOn = True
rpt.Visible = True
rpt.Caption = ReportCaption
'Append it to the collection.
clnClient.Add Item:=rpt, Key:=CStr(rpt.hwnd)
Set rpt = Nothing
The problem is the query is a pass through query for a stored procedure. In my original code I pass the ID to the pass through query, and this will be mch faster as only the records I need will be sent back from the sql server.
The method you suggested will mean I have to download the whole table and then filter it, which is not great performance wise (in my case).
any other suggestions?
EDIT: Just tried doing everything i do in the OpenAClient with the querydefs etc (so before the rpt is actually visible) but the same happens. And if i try to rpt.requery after it, there is a quick flash adn the report closes.
This is normal as you modify the Querydef every time you open the report.
You can open many instances of the report but there is only one querydef used as their recordsource.
What you could do (and what I probably would do) is create a temporary querydef for each instance of the report you open and delete it when you close that instance of the report (or when you remove its handle from the collection, which yields the same result).