Print Reports and Sub Reports

[m2]

I work for a law enforcement agency and have created a database to do
our reports. One of our reports is an operations report that could
have supplemental reports and use of force reports "attached" to it
through an autonumber. If officers fill out a supplemental report,
they can also have a use of force attached to the supplemental report.

All is working and I can print each report separately. I want to
create a function that will allow me to print all reports attached to
the main operations report, to include the proper page header/footer
for each report (as operations/supplemental reports can be multiple
pages per report).

Problem: I have an 'AutoNum' field for each report, and the
supplemental and use of force have an 'AutoFromOR' fields that point
back to the operations report, and the use of force can also link back
to a supplemental report if created from that supplemental.

Desired solution: I would like to click the command button and open
each report, print each according to their AutoNum or AutoFromOR
fields, choose the printer to send it to and then print each one.

Code:
Dim stDocName As String, stWhere As String, stSupp As String, _
stUOF As String, stWSupp As String, stWUOF As String, stUOFSupp As
String, _
stWUOFSupp As String
If Me.Dirty Then Me.Dirty = False
stDocName = "OperationsReport"
stWhere = "SELECT AutoNum FROM " & stDocName & " WHERE AutoNum = "
& Me.AutoNum
stSupp = "SuppReport"
stWSupp = "SELECT AutoFromOR FROM " & stDocName & " WHERE
AutoFromOR = " & Me.AutoNum
stUOF = "UseOfForce"
stWUOF = "SELECT AutoFromOR FROM " & stDocName & " WHERE
AutoFromOR = " & Me.AutoNum
stUOFSupp = "UseOfForce"
stWUOFSupp = "SELECT AutoFromOR FROM " & stDocName & " WHERE
AutoFromOR = " & Me.AutoNum
DoCmd.OpenReport stDocName, acPreview, stWhere, , acIcon
DoCmd.OpenReport stSupp, acPreview, stWSupp, , acIcon
DoCmd.OpenReport stUOF, acPreview, stWUOF, , acIcon
DoCmd.OpenReport stWUOF, acPreview, stWUOFSupp, , acIcon
DoCmd.SelectObject acReport, stDocName, False
DoCmd.RunCommand acCmdPrint
DoCmd.SelectObject acReport, stSupp, False
DoCmd.RunCommand acCmdPrint
DoCmd.SelectObject acReport, stUOF, False
DoCmd.RunCommand acCmdPrint
DoCmd.SelectObject acReport, stUOF, False
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, stDocName, acSaveNo
DoCmd.Close acReport, stSupp, acSaveNo
DoCmd.Close acReport, stUOF, acSaveNo
DoCmd.Close acReport, stUOF, acSaveNo

Any help for my brainfart would be greatly appreciated.

 

[Steve]

Your approach is incorrect. The supplemental report is a subreport of the
operations report and the use of force report is a subreport of the
supplemental report.

Open the supplemental report in design view and add the use of force report
as a subreport. Click on the subreport control (the border around the
subreport) and be sure the Linkmaster and LinkChild properties get filled
in.

Open the operations report in design view and add the supplemental report as
a subreport. Click on the subreport control (the border around the
subreport) and be sure the Linkmaster and LinkChild properties get filled
in.

Now when you print the operations report, if there is a supplemental report,
it will get automatically printed. And if there is a supplemental report
with a use of force report, the use of force report will get automatically
printed too.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[m2]

Thanks for the quick response!

I'm not sure if this is correct, but the operations report can have a
1) use of force, 2) supplemtal report, or 3) supplemental report with
use of force, and I'd like to keep the functionality that way as I
have very, shall I say, non-computer literate co-workers/users. I do
have the relationships all set up as well pointing from the AutoFromOR
of each sub report to AutoNum on the operations report.

I hope this makes sense.

 

[Steve]

With some small tweaks you can make it all work well for you! Your tables
presumably look something like this:
TblOperation
OperationID
<<Operation Data fields>

TblSupplementalToOperation
SupplementalToOperationID
AutoFromOr
<Supplemental to operation data fields>

TblUseOfForceToOperation
UseOfForceToOperationID
AutoFromOr
<Use of force to operation data fields>

TblUseOfForceToSupplemental
UseOfForceToSupplementalID
AutoFromOr
<Use of force to supplemental data fields>

If this is not your table structure, please post back what your tables look
like.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[m2]

OperationsReport
-AutoNum as AutoNumber and Primary Key
-Other Fields

SuppReport
-AutoNum as AutoNumber and Primary Key
-AutoFromOR as Number with a relationship back to the OperationsReport
-Other Fields

UseOfForce
-AutoNum as AutoNumber and Primary Key
-AutoFromOR as Number with a relationship back to the
OperationsReport, but also linked to SuppReport through code:
(In SuppReport code)
Set rs = db.OpenRecordset("UseOfForce")
rs.Fields("AutoFromOR").Value = Me.AutoNum
-Other Fields

The AutoFromOR is the same autonumber created by the primary key in
each field.

 

[Steve]

Your description in your original post implies that a supplemental report is
never filled in on its own. You imply that it is only filled in in
conjunction with an operations report. Further, you imply that an use of
force report may be filled in either in conjunction with an operations
report or supplemental report. The conclusion then is that an use of force
report is only filled out after an operations report is completed; either
immediately following an operations report or after a supplemental report
which follows an operations report. If all of the above are true, then there
is no need to link a use of force report to a supplemental report because a
supplemental report is only completed after an operations report.

Therefore, if you make the supplemental report and use of force report both
subreports of the operations report, they will print automatically if they
exist when an operations report is printed.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[m2]

So, would I be better off when I attach the use of force to the
supplemental (so users don't get confused) that it actually shares the
same AutoFromOR number as the supplemental report, rather than two
different values in the UOF table?

If this is correct, than from my original code, how do I print all
three reports (i.e. 1 OR, 3 Supps and 2 UOF (1 from OR and 1 from
Supp))? And using the DoCmd.RunCommand acCmdPrint, how do I queue all
6 sheets (from i.e.) as separate reports (as to the proper page
headers/footers on each report) and then when my print dialog comes
up, I only have to choose the printer once, and also keeping the code
so if I have 1 OR and 3 Supps, but no UOF how do I keep from printing
blank reports of the report not being used?

Hope I'm not sounding too confusing as it is 01:50 in the morning.

Thanks in advance for your help.

 

[Steve]

<< So, would I be better off ... that it actually shares the same
AutoFromOR number as the supplemental report>> Yes which would be the
primary key value in the operations report.

<<how do I print all three reports>> There would only be the operations
report with the supplemental report and UOF report as subreports. So all you
need to print is the op report and if there is a supplemental report and/or
UOF report, they get automatically printed with the op report.

<<when my print dialog comes up, I only have to choose the printer once>>
The print dialog will only come up once when you want to print the op
report. Keep in mind that you are only printing one report. The supplemental
and UOF reports get printed automatically by virtue that they are
subreports.

I suggest you get this all set up and previously suggested and then print an
op report that has a supplemental report and UOF report. If tweaks are then
necessary, you can go from there. Once you have the three reports working,
you can then try an op report/supplemental report and tweak that if
necessary. Finally you can try an op report/UOF report and tweak that if
necessary.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)