Open Multiple Instances of Report While Using OpenArgs

nckdryr · #1 (**permalink**) 02-05-10, 18:53

When I only needed to open a single instance of a report, I could use the OpenReport method, which offers the option to set the OpenArgs property at the same time. Then I can have some code fire on the report's formatting event to take the OpenArgs, parse them out, and apply the values to various text boxes on the form. It works sufficiently well.

Now I need to use the same report, but let the user open multiple instances. As I understand it, the best way to open multiple instances of a report is to use the Set statement along with the New keyword (though I believe Allen Browne has a thing or two to say about this). I have tried the following code:

Code:

Dim rptX as Report

'event fire

    'do stuff

    Set rptX = New Report_rptName
    rptX.OpenArgs = strOpenArgs
    rptX.SetFocus

But this appears to open the report before allowing for the OpenArgs property to be set. Is it possible to create multiple instances of a report while being able to utilize the OpenArgs property? If not, can anyone suggest a way to get around the OpenArgs property (other than storing the OpenArgs in a temp table, opening the report and pulling these table values)?

nckdryr · #2 (**permalink**) 02-05-10, 19:51

For anyone else's reference, after doing some more digging I found this forum posting. Still don't have a nice solution, but I'll keep working on it.

Sinndho · #3 (**permalink**) 02-06-10, 03:09

I've found this in an application I wrote quite a long time ago:

1. In an independent module:

Code:

Option Compare Database
Option Explicit

Public clnClient As New Collection  'Instances of rptClient.

Declare Function CloseWindow Lib "user32" (ByVal hWnd As Long) As Long
Declare Function DestroyWindow Lib "user32" (ByVal hWnd As Long) As Long
Declare Function MoveWindow Lib "user32" (ByVal hWnd As Long, ByVal x As Long, _
    ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long

Function OpenAClient(ReportCaption As String, ReportFilter As String)
    '
    'Purpose:   Open an independent instance of Report New_Delivery_Schedule.
    '
    Dim rpt As Report
    
    Set rpt = New Report_New_Delivery_Schedule
    rpt.Filter = ReportFilter
    rpt.FilterOn = True
    rpt.Visible = True
    rpt.Caption = ReportCaption
    clnClient.Add Item:=rpt, Key:=CStr(rpt.hWnd)
    Set rpt = Nothing
    
End Function

Function CloseAllClients()

    'Purpose: Close all instances in the clnClient collection.
    'Note: Leaves the copy opened directly from database window.
    
    Dim lngKt As Long
    Dim lngI As Long
    Dim hWnd As Long
    Dim Action As Long
    
    On Error Resume Next
    lngKt = clnClient.Count
    For lngI = 1 To lngKt
        hWnd = CLng(clnClient(1).hWnd)
        Action = DestroyWindow(hWnd)
        clnClient.Remove 1
    Next
    
End Function

2. To open an instance of the report:

Code:

               strCriteria = "FilterID = " & Index
                strSQLFilter = DLookup("Filter", "New_Schedule_Filters", strCriteria)
                strRptName = DLookup("Name", "New_Schedule_Filters", strCriteria)
                strSQL = "SELECT * FROM Schedule_Source WHERE " & strSQLFilter
                If GetRecordCount(strSQL) > 0 Then
                    OpenAClient strRptName, strSQLFilter
                End If

You should be able to adapt it to your needs.

nckdryr · #4 (**permalink**) 02-08-10, 12:59

Hey Sinndho,

I tried taking what you did and adapting it for my own needs (it looked a little like Allen Browne's code that I had tried earlier). But I'm still getting a fatal error whenever I try to open a second instance of a particular report; I don't suppose you have any idea what might cause this error, where I can start looking?

nckdryr · #5 (**permalink**) 02-08-10, 13:39

I've got it figured out (after debugging for a bit, and more than one angry fist-shake at the computer)! For all of you who are interested, here it is:

To get around the OpenArgs issue (the original issue), just simply use a Public Variable, set this before opening an instance of your report, and then have your report look at this variable rather than the OpenArgs argument. Someone may have a cleaner approach, but this worked out best for me.

As for the fatal error above, turns out it was some .Printer settings I had running on the OnOpen event of the report to force the right page size, orientation, margins, etc. (some of my user's were having reports seemingly-randomly change their page size to bizzare sizes, so I had done this earlier). Turns out it still works to do this on the first instance of the report that I open, but on subsequent errors it sends up the fatal error. So I added some code to check if the instance was the first one of the collection, if so, then do the same formatting, otherwise move along:

Code:

    Set rpt = New Report_rptName
    TwipsPerInch = 1440

    If clnReport.Count <= 1 Then
        With rpt.Printer
            .PaperSize = acPRPSLetter
            .Orientation = acPRORLandscape
            .TopMargin = 0.5 * TwipsPerInch
            .BottomMargin = 0.5 * TwipsPerInch
            .LeftMargin = 0.5 * TwipsPerInch
            .RightMargin = 0.5 * TwipsPerInch
        End With
    End If

Hope that helps for anyone with a similar problem. Thanks for the help Sinndho!

Sinndho · #6 (**permalink**) 02-08-10, 14:22

You're welcome and I'm glad you could solve the problem!

pkstormy · #7 (**permalink**) 02-08-10, 19:57

Interesting post and solution.

nckdryr · #8 (**permalink**) 02-09-10, 13:57

Ran into a small problem regarding sub-reports with this technique:

When using the docmd.openreport... method I would set the subreport's recordsource on the OnOpen event, and it worked grand. Now that I'm trying to open multiple instances of a report, I get the attached error 2191 (I haven't changed the subreport's OnOpen code at all). When I debug, the error fires on the line:

Code:

If intCallCount = 0 Then Me.RecordSource = strSQL

I can't figure out why

Any ideas?

Sinndho · #9 (**permalink**) 02-09-10, 14:27

At first sight and as far as I can understand, this happens because though you create several instances of the main report, each on these instances try to open the same an only one instance of the subreport.

If this is the case (I still need to verify my assertion), this means that Access is launching the Open event of the (same) subreport more than once, which triggers the 2191 error ("You can't set the recordsource property after printing has started").

nckdryr · #10 (**permalink**) 02-09-10, 16:20

I thought it had something to do with that as well. But consider this: Say we call the report in question "rptA", and it's respective subreport "rptAsub", where rptAsub is a multi-record report that, say, lists all the affected customers for rptA. Then say we have "rptB" that has a subreport called "rptBsub", which is simply a chart on the report; using the same aforementioned technique for both to open multiple instances, I can open multiple instances of rptB, but not of rptA.

I have verified this to be true, and it simply baffles me. I'm sure it has something to do with what you suggest Sinndho, but I'm just not sure how to fix it. Is it possible to create multiple instances of the problematic subreport and tie an instance to an instance of the parent report?

Sinndho · #11 (**permalink**) 02-09-10, 16:36

Creating multiple instances of the subreport was precisely what I was thinking about. Unfortunately I don't have a usable solution right now, just some ideas of paths that need to be explored.

As for why you can open multiple instances of the report that uses "rptbsub", the first thing that comes to my mind is that "rptBsub" does not iterate through a dataset in order to print one row at a time, but performs some kind of global data processing prior to produce any graphic object.

Once more these ideas are just speculations and I need to test and (in)validate the hypothesis.

Internet Services

Web Development

Digital Marketing

Consumer Tech