By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

How to vary the number of copies of each record in a report in A97?

P: n/a
MLH
I have a source query (qryITSLetterList) feeding rows containing name,
addr, city, state, zip and VehicleID to a report (rptITSnotices). The
query may contain 1-to-5 records resulting in 1-to-5 sheets of paper
coming out of the printer. The report is a form letter. Each row in
the source query results in a single page letter being printed.

Suppose I could include a field in the query [CopiesDesired]. The
value in this field would be from 1-to-4. So if running the query
produced 1 output row, three sheets of paper would print if the
value in the [CopiesDesired] field of that row was 3. Likewise, if
the query dynaset contained 3 rows and the [CopiesDesired] field
values in the rows were 2, 1, 3 respectively - 6 sheets of paper
would exit the printer.

What report property could I set to print varying numbers of copies
when each row was printed? Or, if I'm chasing a pipe-dream, what
method could I employ to produce the desired results?
Dec 18 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
MLH wrote:
I have a source query (qryITSLetterList) feeding rows containing name,
addr, city, state, zip and VehicleID to a report (rptITSnotices). The
query may contain 1-to-5 records resulting in 1-to-5 sheets of paper
coming out of the printer. The report is a form letter. Each row in
the source query results in a single page letter being printed.

Suppose I could include a field in the query [CopiesDesired]. The
value in this field would be from 1-to-4. So if running the query
produced 1 output row, three sheets of paper would print if the
value in the [CopiesDesired] field of that row was 3. Likewise, if
the query dynaset contained 3 rows and the [CopiesDesired] field
values in the rows were 2, 1, 3 respectively - 6 sheets of paper
would exit the printer.

What report property could I set to print varying numbers of copies
when each row was printed? Or, if I'm chasing a pipe-dream, what
method could I employ to produce the desired results?


I won't test it (too much futzing around) but I'd recommend you look at
MoveLayout, NextRecord, PrintSection Properties in help.

I might create a global variable called intCopyCnt. Set the initial
value to 1. Increment CopyCnt by 1 in a footer band. Then I'd compare
it to the CopiesDesired value to the intCopyCnt var in the detail band.
Set NextRecord to False if CopiesDesired greater/= intCopyCnt or True
if less than intCopyCnt.

You will need to futz around with the NextRecord property and setting
your breakpoints (you may find a footer band for detail will assist) but
it should be able to handle your problem.
When Copy increment the count by 1. If equal, reset the counter to 1.
In the header, I might compare the CopiesDesired to the intCopyCnt
variable.
Dec 18 '05 #2

P: n/a
On Sun, 18 Dec 2005 13:02:52 -0500, MLH <CR**@NorthState.net> wrote:
I have a source query (qryITSLetterList) feeding rows containing name,
addr, city, state, zip and VehicleID to a report (rptITSnotices). The
query may contain 1-to-5 records resulting in 1-to-5 sheets of paper
coming out of the printer. The report is a form letter. Each row in
the source query results in a single page letter being printed.

Suppose I could include a field in the query [CopiesDesired]. The
value in this field would be from 1-to-4. So if running the query
produced 1 output row, three sheets of paper would print if the
value in the [CopiesDesired] field of that row was 3. Likewise, if
the query dynaset contained 3 rows and the [CopiesDesired] field
values in the rows were 2, 1, 3 respectively - 6 sheets of paper
would exit the printer.

What report property could I set to print varying numbers of copies
when each row was printed? Or, if I'm chasing a pipe-dream, what
method could I employ to produce the desired results?


Use a cartesian join.
Create a table tblCopies with a single field (CopyNo).
Include this table in the recordsource of the report (with no joins to any other table).
Before the report prints seed tblCopies with one record for each copy required. ie If the user wants 3 copies, add 3
records to tblCopies.

When the report is run, because it's recordsource cannot determine any relationship between tblCopies and any other
table in the recordsource, a duplicate row will be produced for each record in tblCopies. This will result in x copies
of each record printed.

Wayne Gillespie
Gosford NSW Australia
Dec 19 '05 #3

P: n/a
MLH
>
I won't test it (too much futzing around) but I'd recommend you look at
MoveLayout, NextRecord, PrintSection Properties in help.

I might create a global variable called intCopyCnt. Set the initial
value to 1. Increment CopyCnt by 1 in a footer band. Then I'd compare
it to the CopiesDesired value to the intCopyCnt var in the detail band.
Set NextRecord to False if CopiesDesired greater/= intCopyCnt or True
if less than intCopyCnt.

You will need to futz around with the NextRecord property and setting
your breakpoints (you may find a footer band for detail will assist) but
it should be able to handle your problem.
When Copy increment the count by 1. If equal, reset the counter to 1.
In the header, I might compare the CopiesDesired to the intCopyCnt
variable.

Salad, there's no A97 sample code in the HELP on these properties.
Have you looked at any on the web you think is particularly helpful?
Dec 19 '05 #4

P: n/a
MLH wrote:
I won't test it (too much futzing around) but I'd recommend you look at
MoveLayout, NextRecord, PrintSection Properties in help.

I might create a global variable called intCopyCnt. Set the initial
value to 1. Increment CopyCnt by 1 in a footer band. Then I'd compare
it to the CopiesDesired value to the intCopyCnt var in the detail band.
Set NextRecord to False if CopiesDesired greater/= intCopyCnt or True
if less than intCopyCnt.

You will need to futz around with the NextRecord property and setting
your breakpoints (you may find a footer band for detail will assist) but
it should be able to handle your problem.
When Copy increment the count by 1. If equal, reset the counter to 1.
In the header, I might compare the CopiesDesired to the intCopyCnt
variable.


Salad, there's no A97 sample code in the HELP on these properties.
Have you looked at any on the web you think is particularly helpful?


I've used PrintSection, MoveLayout, and NextRecord quite a few times.
But it usually takes some futzing.

For help, I usually go to http://groups.google.com/advanced_search?hl=en.

I enter my keywords...in this case
PrintSection NextRecord
and in the groups box I enter
*access*
and I came up with lots of hits. Here's one. Don't know if this will
wordwrap.
http://groups.google.com/group/micro...e128708292cb3e
Dec 19 '05 #5

P: n/a
MLH
sorry, I should-a-googled first.
Dec 19 '05 #6

P: n/a
Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:an********************************@4ax.com:
Use a cartesian join.
Create a table tblCopies with a single field (CopyNo).
Include this table in the recordsource of the report (with no
joins to any other table). Before the report prints seed tblCopies
with one record for each copy required. ie If the user wants 3
copies, add 3 records to tblCopies.


Actually, wouldn't it make sense to have the table prepopulated with
a single field with values 1, 2, 3, 4, 5, 6..., and then you'd have
a WHERE clause on that table <=3 for 3 copies, =1 for 1 record, and
so forth.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 19 '05 #7

P: n/a
On Mon, 19 Dec 2005 14:15:49 -0600, "David W. Fenton" <XX*******@dfenton.com.invalid> wrote:
Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:an********************************@4ax.com :
Use a cartesian join.
Create a table tblCopies with a single field (CopyNo).
Include this table in the recordsource of the report (with no
joins to any other table). Before the report prints seed tblCopies
with one record for each copy required. ie If the user wants 3
copies, add 3 records to tblCopies.


Actually, wouldn't it make sense to have the table prepopulated with
a single field with values 1, 2, 3, 4, 5, 6..., and then you'd have
a WHERE clause on that table <=3 for 3 copies, =1 for 1 record, and
so forth.


Whatever rocks your boat.

Wayne Gillespie
Gosford NSW Australia
Dec 19 '05 #8

P: n/a
Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:pl********************************@4ax.com:
On Mon, 19 Dec 2005 14:15:49 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:
Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:an********************************@4ax.co m:
Use a cartesian join.
Create a table tblCopies with a single field (CopyNo).
Include this table in the recordsource of the report (with no
joins to any other table). Before the report prints seed
tblCopies with one record for each copy required. ie If the user
wants 3 copies, add 3 records to tblCopies.


Actually, wouldn't it make sense to have the table prepopulated
with a single field with values 1, 2, 3, 4, 5, 6..., and then
you'd have a WHERE clause on that table <=3 for 3 copies, =1 for 1
record, and so forth.


Whatever rocks your boat.


Actually, I wouldn't do either of these. I'd just use the formatting
events to control whether I moved to the next record or not. I've
got label reports that already do this, and got the code out of the
MS KnowledgeBase, but that was way back in Access 2.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 20 '05 #9

P: n/a
On Tue, 20 Dec 2005 15:05:15 -0600, "David W. Fenton" <XX*******@dfenton.com.invalid> wrote:
Actually, I wouldn't do either of these.


That makes me feel better ;)

Wayne Gillespie
Gosford NSW Australia
Dec 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.