Open same report multiple times

[Chuck]

I have a single report that is used to display the results of a form. The
record source of the report may change depending which check box is selected
in the form. The record source is passed to the report via OpenArgs
(DoCmd.OpenReport "MyReport", acViewNormal, ,,, Args)

To print all instances of the report I use the following code in the form:

DoCmd.OpenReport "MyReport", acViewNormal, ,,, Args1
DoCmd.OpenReport "MyReport", acViewNormal, ,,, Args2
DoCmd.OpenReport "MyReport", acViewNormal, ,,, Args3

This works fine when printing directly to the printer.

Now I want to preview all the reports before printing:

DoCmd.OpenReport "MyReport", acViewPreview ,,, Args1
DoCmd.OpenReport "MyReport", acViewPreview ,,, Args2
DoCmd.OpenReport "MyReport", acViewPreview ,,, Args3

As expected, this only opens the last report in print preview (since the
same report is used for all three reports, the first one is overriden by the
second one, the second one by the third, and only the last one shows up).

How can I get the same report to open more than once in print preview mode ?
Is there a way to create a copy of the report on the fly ?

Thanks

 

[Allen Browne]

You can open multiple instances of a report with the New keyword, i.e.:
Dim rpt1 As Report
Set rpt1 = New Report_MyReport
rpt1.Visible = True
rpt1.Filter = Args1
rpt1.FilterOn = True

However the instances will go out of scope as soon as the code has run if
the variables go out of scope. The solution is to declare a custom
collection, and add the instances to your collection.

There is an example of how to do that with forms in this link:
Managing Multiple Instances of a Form
at:
http://allenbrowne.com/ser-35.html

However, this does not work well with reports in my experience, e.g. Access
can get confused between the instances while you are opening them.

 

[Chuck]

Can I pass other variables from the form to the report using this method?
And is the form's OnOpen event executed when opening each instance?

Thanks

 

[Allen Browne]

You cannot pass a WhereCondition or OpenArgs when you open report instances
with the New keyword.

Report_Open works for me, though I have seen others who claim it does not
fire reliably.

Attempting to set the Filter of the report instance in Report_Open will
almost certainly fail to work correctly. Sometimes you get no filter;
sometimes it gets applied to the next instance you open. Sprinkling some
DoEvents seems to help jag it sometimes, but it is not something you can
rely on. That's why the example below sets the report instance's Filter
after it opens: although that is normally an inefficient thing to do (the
query runs twice), it's about the only thing that provides any consistency
when working with multiple instances.

As I said, the whole darn thing is so flakey that you are walking on air -
especially if you expect it to continue working with future version of
Access, Windows, and future hardware that performs faster and with more
multi-threading options.

 

[Chuck]

Do you recommend declaring a global variable that I can use to pass the data
from the form to the report? I tried it with the database you have on your
website and it works fine.

To make things simpler here, I added the appropriate code directly in the
form instead of using the function. The my_recordsource variable is
declared in the modules section of the database.

' Prodecure in frmMain
Private Sub cmdOpenAClient_Click()

Dim frm As Form

my_recordsource = "tblClient_1"

'Open a new instance, show it, and set a caption.
Set frm = New Form_frmClient
frm.Visible = True
frm.Caption = frm.Hwnd & ", opened " & Now()

'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)

Set frm = Nothing

my_recordsource = "tblClient_2"

'Open a new instance, show it, and set a caption.
Set frm = New Form_frmClient
frm.Visible = True
frm.Caption = frm.Hwnd & ", opened " & Now()

'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)

Set frm = Nothing

End Sub

' Procedure in frmClient
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = my_recordsource
End Sub

 

[Allen Browne]

That should be fine with forms.

(Not sure why you have tables named tblClient_1, tblClient_2, etc, but
perhaps that's just an example.)