Use one report with two queries

[Peter]

I have a complicated report the gets its data from a query, it works
well. I now wish the use a copy of my report with a different query, ie
with a different Control source. I know I can specify the query or
Table to be used in my report when it is first set up but how do I
change the query to be used for a report without designing a new, but
identical, report?

 

[Roger Converse]

Hello,

All you need to do to copy a report is right click on the report, click
copy, right click again (not on an existingreport) and click paste. You
should be prompted as to what you want to call this new report.

Once the report is copied, you can go into the properties of the new report
and switch out the queries. Now, you'll want to make sure you pay attention
to the field names. If those are not the same between the queries, you will
want to address that (i.e. query1 field = DOB & query2 field = BirthDate).

HTH,
Roger

 

[George Nicholson]

You can certainly swap out different queries as Recordsources for a single
report.
Of course, the queries have to contain the same field names, etc., or the
results will be ugly (if there are results).

One approach:

1) When you open the report, pass the name of the query you want to use via
the OpenArgs argument of the OpenReport method:
If chkBox1 then
DoCmd.OpenReport "MyReport",,,,,"qryOneName"
End if

If chkBox2 then
DoCmd.OpenReport "MyReport",,,,,"qryTwoName"
End if

Then, in the Open event of the Report
Me.Recordsource = Me.OpenArgs

If you want to change any labels you can do that in the Open event as well:

Select Case Me.OpenArgs
Case "qryOneName"
me.lblReportHeader.Caption = "Query One Report"
Case "qryTwoName"
me.lblReportHeader.Caption = "Query Two Report"
Case Else
'Do Nothing
End Select

As I recall, OpenArgs for *reports* was not available in Access 97 and
earlier. I'm not 100% sure when it was added, certainly by 2002/XP, but I'm
at all sure about its availability in 2000.

 

[Klatuu]

It was not available in 2000. I don't know about 2002, but it is in 2003

 

[George Nicholson]

I can confirm 2002/xp, so that's when it was added. Thanks.

 

[Klatuu]

Thanks, George. I was wondering about that.