Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: Changing recordset with Querydefs with multiple instances?

    HI. I;'ve got a report where the On Open event is:
    Code:
    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"
     
        qdf.Close
        db.Close
        Set qdf = Nothing
        Set db = Nothing
    End Sub
    This works fine when I simply use
    Code:
    DoCmd.OpenReport "rptRM", acViewReport, , , acWindowNormal
    Now I'm trying to be able to open multiple instances of the same report.

    I'm using Allen Browne's code from Microsoft Access tips: Managing Multiple Instances of a Form

    THe problem is, when I open a report, it uses the query as its recordset before it changes the query, and so the recordset is that of the previously viewed report.

    THe code Allen uses to open the report is:
    Code:
    Set rpt = New Report_rptRM
             
        rpt.Visible = True
        
        'Append it to our collection.
        clnRM.Add Item:=rpt, Key:=CStr(rpt.Hwnd)
    WHat's the difference between these two ways of opening a report?? I am guessing it's something to do with acViewReport or acWindowNormal as these are not defined in Allens code.

    Any IDeas??

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:

    Code:
    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
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jun 2009
    Posts
    89
    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.
    Last edited by rudeboymcc; 07-08-09 at 10:40.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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).

    Have a nice day!

  5. #5
    Join Date
    Jun 2009
    Posts
    89
    I've found a very crude way of doing this. From what I understood I needed to just close and reopen a report, but i couldnt; do this from the function because it would close all the report instances.

    Instaead I did the folowing:
    Code:
    Set rpt = New Report_rptRM
             Set rpt = New Report_rptRM
        rpt.Visible = True
        
        'Append it to our collection.
        clnRM.Add Item:=rpt, Key:=CStr(rpt.Hwnd)
    So by setting rpt twice for some reason it solves all the problems I have.

    Note there's no code betwene them. WHat coudl cause that?

    I'm not really sure how to remove a handle from a query def without closeing the report.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •