Solved

In MS Access, can I print multiple copies of a report with each being slightly different?

Posted on 2016-09-01
35
28 Views
Last Modified: 2016-09-06
My reports print in triplicate.  There are the 'blues', the 'reds', and the 'yellows'
At present they print via a loop that boils down to
with rs1
    for x = 1 to 3
        DoCmd.OpenReport stDocName, acNormal
        .edit
        !AFieldThatChangesTheColor = x
        .Update
    Next x
end with

Open in new window


This ultimately issues 3 separate
DoCmd.OpenReport stDocName, acNormal
commands resulting in the report being completely rendered three times.
All that changes between renderings is the background color of one control.

Is this the only way the required end result can be accomplished?
Or can something be coded in the report itself that will allow
DoCmd.PrintOut acPrintAll, , , acHigh, 3
to print out the required triplicate of blue, red and yellow?
Or any other method that won't require 3 time-expensive renderings?
0
Comment
Question by:Nick67
  • 12
  • 10
  • 8
  • +1
35 Comments
 
LVL 18
Comment Utility
you can make a Numbers table with three records

Numbers
- Num, number, long integer

create records for Num = 1 to 31 (that way, you can use it for days of a month too, if you ever have a need)

in the RecordSource for the query or SQL that the report is based on, add the Numbers table but don't link anything to it.

On the grid, add:

field --> Num
Criteria --> <=3

this will get you 3 copies of everything ;)
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
And your report coloration code can use the Number to assign blue, red, or yellow.

Great idea Crystal.  I also use tables like that for many things.
1
 
LVL 26

Author Comment

by:Nick67
Comment Utility
this will get you 3 copies of everything ;)

I'm not so sure of that.
You are suggesting a cartesian join for the recordsource.
That will certainly get me three times as many records.
But I need the whole report -- and that would include ReportHeader and ReportFooter -- rendered 3 times.

But tripling the number of records in the query and printing the report once won't do that, will it?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It will if you add a break on the "number".  Make it your primary page break.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
if you add a new number field to the master table (default set to 1) and to the proposed 'numbers' table, then JOIN on the new field, you wont get a Cartesian product, just the number of matching records
0
 
LVL 18
Comment Utility
yes, as Pat suggested, group by the Num (ie you call it that) field. Set Page Break Before to be true for that section and make it the highest level group.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
I was thinking 3 records in the new table, 1,2 and 3, and on the report current event, if 1 then red ...
conditional formatting
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
@COACHMAN99
Reports do not have Current() events.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
they do
Private Sub Report_Current()

End Sub

but the event is superfluous as the conditional formatting will do it
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
For what matters in this case, they don't no current
And from my understanding, they will only fire when Report view is in play
https://social.msdn.microsoft.com/Forums/office/en-US/692acbd1-e1ac-4cbc-bc2a-2016260557ef/report-oncurrent-event-not-firing?forum=accessdev
and not in print preview or straight print.
A lot of code in Report_Open deosn't fire in a straight print.
Highly Annoying.
Try setting the report caption in the Open event and then look at what goes into the print queue on a print-from-preview vs a straight print :(

But that, in this particular case, is irrelevant as there is (as shown) no current event in any case.
I'll have to build a sample and see what can be accomplished.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
Maybe its my version of Access (2016) that displays it

I normally use joined tables and conditional formatting to achieve your objective.
good luck
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
Maybe its my version of Access (2016) that displays it
Everything A2007+ that had a Report view will display it.
Usability seems a bit patchier.

So In A2016, if you put code in Current, and print the report with
DoCmd.OpenReport stDocName, acNormal
does the code fire?

And in A2016, if you build a report and give it a caption of "Not Fired" and if you put this in the Report_Open code
Me.Caption = "I will fire"
pause your default printer and print it with
DoCmd.OpenReport stDocName, acNormal
what print job name winds up in the paused printer queue?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Nick,
You don't need code in any event.  Just add the table with the three rows to your RecordSource query.  You can add more rows to make it useful for other things but then you'll need criteria to limit it to three.  Use conditional formatting to do the "color" part.  Just add three conditions.  One for each color option.  It doesn't matter of the query is a Cartesian product since you can't update from the report anyway.  If this is a query you use for multiple things and so you don't want to change it, just make a new query that selects the old query and joins it to the numbers table.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
SORRY, I AM OUT OF SYNC.
not sure without testing all the above.


does conditional formatting based on the new JOINED table/fields not solve the original problem?
0
 
LVL 18
Comment Utility
conditional formatting rules are not considering Num -- so they should be just fine. Nick -- have you tried using a Numbers table? Until you do, any other comments are pointless because this will work.

btw, Report View does not run VBA code -- only Print Preview and Print (Normal).

As Pat mentioned, you do not need code to get multiple copies using the Numbers table method.
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
Close.
No cigars, though.
I need the report headers and report footers to repeat.
They do not.
Sample attached.

I suspect I could create a Group header and footer for num and move all the objects and code that are in the Report header and footer to these new items to get the result

But that's a major re-arrangement of existing items (and lots of them, some of which are creaking at the edge of what Access likes already)

Or did I miss something?
How did you get Report Headers and Footers to render multiple times?
CartesianJoin.mdb
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Report headers and footers print only ONCE for a report.  This won't change that.  If you need something to print for each of the three reports, you will have to add an additional group as I believe I mentioned.  Move your header/footer controls to the new section.  Make the new section bread on number.

PS - I have probably used a report header twice in 20 years.  Report Footers are more valuable since they are frequently used for recaps.  But a new group will solve the problem.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Author Comment

by:Nick67
Comment Utility
Report headers and footers print only ONCE for a report.
That's what I figured
And questioned
this will get you 3 copies of everything ;)

I'm not so sure of that.

If you need something to print for each of the three reports, you will have to add an additional group as I believe I mentioned.
You did:
It will if you add a break on the "number".  Make it your primary page break.
And I added it (the Num header) and did Force Page Before.

But as suspected, it didn't get me the Report header and footer.
I didn't think it would.

It was a good initial thought
What about the original report as an unbound subreport, with the new main report just having Num for records to run through?
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
sorry - please disregard this - I believe Pat covered it.
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 100 total points
Comment Utility
>"How did you get Report Headers and Footers to render multiple times?"

As mentioned by others, the Report Header and Footer only print once -- but the controls can be moved to a group section.  If you have a lot of reports and don't want to take the time to do this then:
if you are rending to a printer, you can set the default number of copies for the printer. This can be done programmatically before the report is printed and set back when done.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
except wont they all be the same color if you print multiple copies?
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
@Crystal
As mentioned by others, the Report Header and Footer only print once
Others including myself when you said
this will get you 3 copies of everything ;)
I didn't think it would, and it doesn't

you can set the default number of copies for the printer.
except wont they all be the same color if you print multiple copies?
Yes, I think they will, and that isn't the idea.
The idea is to get three entire reports with slight differences out in  a more efficent way than
with rs1
    for x = 1 to 3
        DoCmd.OpenReport stDocName, acNormal
        .edit
        !AFieldThatChangesTheColor = x
        .Update
    Next x
end with

Open in new window


and without massively re-architecting them.

IF that is possible.
0
 
LVL 18
Comment Utility
I will now defer to others as I have given you an alternative (set printer copies) as well.
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 100 total points
Comment Utility
I'm not sure if I am imagining this but it seems we are beating this to death?
Surely, if you join another table with 3 matching records to your data source, and group on the new color-control-field, then you will get 3 almost identical copies of the report?
And, if you conditionally format on the color-control field, you will get 3 reports with a different color on each? Am I missing something significant?
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
@COACHMAN99

The report headers and footers will only print ONCE for an issuance of
DoCmd.OpenReport stDocName, acNormal
And I have report headers and footers.
So, no the technique first proposed won't work.
I get a reportheader, then three copies of everything and then a reportfooter.
So, they are not identical
The first page has the report header but no of the others do.
The last page has the report footer but none of the others do.
0
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
I realize this may have been asked earlier butthe text is getting too long. Are the headers and footers in the primary grouping? (In this case the color control field) and not in the report header?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 300 total points
Comment Utility
@Nick,
You're being more trouble than a novice.  I told you initially that you would probably need to add a group header.  Since I never use Report Headers and rarely use Report footers, it didn't occur to me to tell you to move your report header and footer controls (which I didn't know about) to the new group header and footer.

The table is a no code solution to your problem.  All you have to do is to move your report header/footer controls to the new group header/footer sections.  And then the solution does work as we suggested.  Use the conditional formatting to add the color.

I updated your report
CartesianJoinUpdated.mdb
1
 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
Hallelujah brother
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
Comment Utility
Sigh.
I know I am being more trouble than a novice becasue I am not asking a novice question.
I'll close this out and either give up the effort for now, or try a more pointed question later.
Reformulating 20+ reports by moving the contents of the report headers and footers, and rebuilding the code that does extensive stuff to them into new sections and ensure that the first topmost header prints once, and never again while the bottommost footer doesn't print unitl the last page (and we all know how [Page] of [Pages] likes to lie) will not be simple.

I was hoping for some insight into the mechanics of
DoCmd.PrintOut
If there were code in the report footer would that fire between each copy, or does something at a lower level take that already rendered object and just duplicate it as WYSIWYG.

Thanks you for your thoughts, time and effort, though.
They are appreciated, as always.

Nick67
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Yes, you have to move the code to a new section, yes you have to move the controls to a new section.  Why not try it for one report to see if other problems are created.

PrintOut is pretty stupid.

Another alternative that does require VBA,

Using the table method gives you a no code solution.  But, if you want to make a code loop to export the colors one at a time, create a hidden form field that you increment 1, 2, 3 in the loop.  Then in your conditional formatting, reference the form field.  That should work with what you have now.
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
@Pat
But, if you want to make a code loop to export the colors one at a time, create a hidden form field that you increment 1, 2, 3 in the loop.  Then in your conditional formatting, reference the form field.  That should work with what you have now.
with rs1
    for x = 1 to 3
        DoCmd.OpenReport stDocName, acNormal
        .edit
        !AFieldThatChangesTheColor = x
        .Update
    Next x
end with

Open in new window


Basically, that's what is already on the go.
It is operationally important to track copies printed, date and time done so, computer they were prnted from, and the logged on user who printed, so the number goes to a table, and is part of the recordsource for the report
But it requires a full three renderings of the report within the loop, and I was looking for something more time-efficient than that.

Instantiating reports as objects can be around ~40% faster than a full-blown open (whether that's because instantiated objects cannot be thrown into design view and so the overhead for Access is less or why that should be so, I don't know.  And I haven't conducted rigourous testing of various reports with varying data to see whether that opening speed has dependencies on as-of-yet unknown factors)
<grin> Code doesn't scare me.  I've only got about 110,000 lines of it at the moment, what's a few more?</grin>

But re-architecting some of these would be a bear.  I have one with 11 different sub-reports on it.
It positively creaks.  When I am forced to alter that one, I put the file in the /decompile folder that has a nice shortcut to do the command line decompile, because altering invariably crashes Access as I mess with it and then a decompile is a necessity (it's a highly complex report, an engineering certification that absolutely has to print on a single page)

PrintOut is pretty stupid.
That was what I was wondering, is it stupid and just renders what's previewed to the printer, or does the code re-execute for each rendering so that code in the reportfooter Print event could execute the table update and result in three different outcomes.

I'll have to play with it.
Thank you for your time and effort.

Nick
0
 
LVL 26

Author Closing Comment

by:Nick67
Comment Utility
The question as asked did not receive and Answer.
Extensive reformulation of the existing report may allow the desired result to be achieved but the difficulty inherent in successfully detecting the last page in an ironclad way makes that a very difficult thing to execute.

Thanks to all who contributed.
I appreciate your time and efforts
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I disagree Nick.  The question did receive an answer and I even modified your test database to show you that it does in fact work.  You just didn't like the solution.  It doesn't matter how many subreports a report has since you only need to modify the main report to move controls from one section to another.  Using the proposed solution would be slightly more efficient since it renders the report once, albeit with three sets of data.  The method you are using and that I recommended last, renders three separate reports and runs three queries.   The report object is smart enough to be able to print the same object three times.  It is not capable of printing slightly different variations of that object each time.  So either the answer is - Access cannot do it (and I seriously doubt that any other tool could do it).  Or, Access can do it but you don't like the answer.
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
Ok Pat,

This was the guts of the question, in my mind.
Or can something be coded in the report itself that will allow
DoCmd.PrintOut acPrintAll, , , acHigh, 3
 to print out the required triplicate of blue, red and yellow?

The answer offered to that is 'if you eliminate report headers and footers and change the recordsource of the report to a cartesian query, you can get the effect you want.'
Ok.

So either the answer is - Access cannot do it (and I seriously doubt that any other tool could do it).  Or, Access can do it but you don't like the answer.

Good enough.
I figured I would ask, since maybe there was something simple out there I had overlooked.
If you don't ask, you'll never know.

Thanks,
Nick
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You're welcome.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now