Dynamic report titles

[Jake55923]

Hello,
I am trying to print a series of 11 reports where each report corresponds to a certain department. I would like to use only one report ("rpt Incomplete" in my code), but have a dynamic title that corresponds to the correct department on the printed reports. For example, the first printed report should be titled "Airt Texturing" & the second would be "Engineering Services". Here is my code so far:


Private Sub Print_Click()
Dim qdf As QueryDef, strSQL As String

strSQL = "SELECT * FROM [tbl Safety Audit Tracking] WHERE [Department] = 'Air Texturing' ORDER BY [tbl Safety Audit Tracking].[Date Discovered]"
Set qdf = CurrentDb.QueryDefs("qry Incomplete")
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenReport ("rpt Incomplete"), acViewNormal

strSQL = "SELECT * FROM [tbl Safety Audit Tracking] WHERE [Department] = 'Engineering Services' ORDER BY [tbl Safety Audit Tracking].[Date Discovered]"
Set qdf = CurrentDb.QueryDefs("qry Incomplete")
qdf.SQL = strSQL
qdf.Close
DoCmd.OpenReport ("rpt Incomplete"), acViewNormal

End Sub

Let me know if more info is necessary. Thanks!

 

[dcx693]

First, which version of Access are you running? If you're using 2002 or higher, the DoCmd.OpenReport method has an OpenArgs parameter that you can use to pass the proper name of the report to the report.

If you're running 2000 or earlier....where are you running the code from? A form? Or a standalone module? If you're using a form, you can easily set the value of a hidden control, or even the form's tag property to the name of the report. When the report opens, have some code in the report Open or Load event pick up the report name from the form that is still open. If you're running it from a module, you can set the value of a public string variable to the name of the report.

 

[Jake55923]

I am running 2000. The code is run from a form where the user clicks the print button & ideally 11 reports would print, each with the appropriate title. Could you explain more about the code in the report's load or open event that would put the correct title in place?

Thanks a lot.

 

[pbaldy]

Since your SQL includes:

WHERE [Department] = 'Air Texturing'

I would think if you have the [Department] field as the control source of a textbox in the header, you'll have your title. No code required. Or have I misunderstood?

 

[DALeffler]

It seems to me that you need one 11 page report and not 11 seperate 1 page reports.

IOW, the query for the report should eliminate the Where clause so as to grab all the data for all the departments into one query.

Then use the report Sorting and Grouping to print individual page headers according to Department titles from the query.

hth,

 

[Jake55923]

Doug - You were right...thanks so much