Print selected record multiple times

[Blondyeee]

I need to create a command button on a form that will allow me to print the
single record I am viewing 3 times on one page (need to be cut apart for 3
different uses). I am a very inexperienced user. I was able to create a
command button to print a single record, however, I need it to appear on the
page 3 times.
Thanks!

 

[fredg]

I need to create a command button on a form that will allow me to print the
single record I am viewing 3 times on one page (need to be cut apart for 3
different uses). I am a very inexperienced user. I was able to create a
command button to print a single record, however, I need it to appear on the
page 3 times.
Thanks!

In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:


In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:



In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:


The above will print the same values 3 times in the detail section for
each record.

Then, to print just the one record you are viewing in the form....

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview , , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records

 

[Ken Sheridan]

You can either do as Fred suggests, or for a more flexible way of printing
multiple copies of a record do the following.

1. Create a table NumbersToPrint with a single column NumberToPrint of
number data type. Add rows to this table with numbers from 1 to whatever is
the highest number of each record you might want to print at one time.

2. Create a query which includes your current table and the NumbersToPrint
table, but don't join the tables. This creates what's known as the Cartesian
product of the two tables, which simply means that every row in one is joined
to every row in the other.

3. Add the columns from your current table to the query, and also add the
NumberToPrint column. In its 'criteria' row put the following:

<= [Number of copies of record to print?]

If you want it to default to 3 then put something like this:

<= Nz([If not 3 then enter number of copies of record to print:],3)

Pressing Enter at the prompt without specifying any number will then print 3
instances of the record by default.

4. Create a report based on this query (you can use the report wizard to
save time). In this case add one set of controls for each field in the usual
way. If you want a fixed number of records to print on each page, e.g. 3,
then size the detail section so that 3 will print per page.

5. To print just the current record add a button to the form using code as
Fred described. When the report opens you'll be prompted to enter the number
of copies to print, so you can print variable numbers of each record simply
by responding to the prompt.

6. However, the same report can be used to print more than one record, in
which case it will print however many of each you enter at the prompt.
Whether each set of identical records is printed together, or it prints a set
of all records, then another set and so on, depends on how you sort the
report using its internal 'sorting and grouping' mechanism. If you sort on
the primary key of your current table, RecordID in Fred's example, and then
on the NumberToPrint column it will do the former; if you sort first on the
NumberToPrint column and then on the primary key of your current table, it
will do the latter.

Ken Sheridan
Stafford, England

 

[Blondyeee]

You are the best!!! Thank you for making me look like a hero to my client. I
have spent two days working on this before stumbling on this forum.

Many Thanks!!
Holly

I need to create a command button on a form that will allow me to print the
single record I am viewing 3 times on one page (need to be cut apart for 3
different uses). I am a very inexperienced user. I was able to create a
command button to print a single record, however, I need it to appear on the
page 3 times.
Thanks!

In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:


In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:



In the report's Detail Section ... Create a report and simply add the
same controls (with identical control sources) to the report 3 times,
laid out in 3 different row positions in the report. Like this:


The above will print the same values 3 times in the detail section for
each record.

Then, to print just the one record you are viewing in the form....

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview , , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records

 

[v_fas]

How do you use this method to print an invoice 3 times on one sheet? I
cannot move everything into the detail section, as then there is no sorting
or grouping in effect; ie, it would only print out the first item in the
order.

You can either do as Fred suggests, or for a more flexible way of printing
multiple copies of a record do the following.

1. Create a table NumbersToPrint with a single column NumberToPrint of
number data type. Add rows to this table with numbers from 1 to whatever is
the highest number of each record you might want to print at one time.

2. Create a query which includes your current table and the NumbersToPrint
table, but don't join the tables. This creates what's known as the Cartesian
product of the two tables, which simply means that every row in one is joined
to every row in the other.

3. Add the columns from your current table to the query, and also add the
NumberToPrint column. In its 'criteria' row put the following:

<= [Number of copies of record to print?]

If you want it to default to 3 then put something like this:

<= Nz([If not 3 then enter number of copies of record to print:],3)

Pressing Enter at the prompt without specifying any number will then print 3
instances of the record by default.

4. Create a report based on this query (you can use the report wizard to
save time). In this case add one set of controls for each field in the usual
way. If you want a fixed number of records to print on each page, e.g. 3,
then size the detail section so that 3 will print per page.

5. To print just the current record add a button to the form using code as
Fred described. When the report opens you'll be prompted to enter the number
of copies to print, so you can print variable numbers of each record simply
by responding to the prompt.

6. However, the same report can be used to print more than one record, in
which case it will print however many of each you enter at the prompt.
Whether each set of identical records is printed together, or it prints a set
of all records, then another set and so on, depends on how you sort the
report using its internal 'sorting and grouping' mechanism. If you sort on
the primary key of your current table, RecordID in Fred's example, and then
on the NumberToPrint column it will do the former; if you sort first on the
NumberToPrint column and then on the primary key of your current table, it
will do the latter.

Ken Sheridan
Stafford, England

I need to create a command button on a form that will allow me to print the
single record I am viewing 3 times on one page (need to be cut apart for 3
different uses). I am a very inexperienced user. I was able to create a
command button to print a single record, however, I need it to appear on the
page 3 times.
Thanks!

 

[John Spencer]

PERHAPS, by adding top-level group based on the NumbersToPrint value. You can
group on that number, but if desired set the visible property of the group to No.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

How do you use this method to print an invoice 3 times on one sheet? I
cannot move everything into the detail section, as then there is no sorting
or grouping in effect; ie, it would only print out the first item in the
order.

You can either do as Fred suggests, or for a more flexible way of printing
multiple copies of a record do the following.

1. Create a table NumbersToPrint with a single column NumberToPrint of
number data type. Add rows to this table with numbers from 1 to whatever is
the highest number of each record you might want to print at one time.

2. Create a query which includes your current table and the NumbersToPrint
table, but don't join the tables. This creates what's known as the Cartesian
product of the two tables, which simply means that every row in one is joined
to every row in the other.

3. Add the columns from your current table to the query, and also add the
NumberToPrint column. In its 'criteria' row put the following:

<= [Number of copies of record to print?]

If you want it to default to 3 then put something like this:

<= Nz([If not 3 then enter number of copies of record to print:],3)

Pressing Enter at the prompt without specifying any number will then print 3
instances of the record by default.

4. Create a report based on this query (you can use the report wizard to
save time). In this case add one set of controls for each field in the usual
way. If you want a fixed number of records to print on each page, e.g. 3,
then size the detail section so that 3 will print per page.

5. To print just the current record add a button to the form using code as
Fred described. When the report opens you'll be prompted to enter the number
of copies to print, so you can print variable numbers of each record simply
by responding to the prompt.

6. However, the same report can be used to print more than one record, in
which case it will print however many of each you enter at the prompt.
Whether each set of identical records is printed together, or it prints a set
of all records, then another set and so on, depends on how you sort the
report using its internal 'sorting and grouping' mechanism. If you sort on
the primary key of your current table, RecordID in Fred's example, and then
on the NumberToPrint column it will do the former; if you sort first on the
NumberToPrint column and then on the primary key of your current table, it
will do the latter.

Ken Sheridan
Stafford, England

I need to create a command button on a form that will allow me to print the
single record I am viewing 3 times on one page (need to be cut apart for 3
different uses). I am a very inexperienced user. I was able to create a
command button to print a single record, however, I need it to appear on the
page 3 times.
Thanks!

 

[KenSheridan via AccessMonster.com]

I assume you'd want something like InvoiceNumber, InvoiceTo and InvoiceDate
in the header and then the individual invoice lines in the detail. In which
case base the report on the Invoices table joined to the NumbersToPrint table
so it returns three lines per invoice. Create a subreport based on the
InvoiceLines table and include this and everything else in the detail section
of the parent report, linking the subreport on InvoiceNumber and sorting the
parent report on whatever column from Invoices you wish. If you sort by a
column other than the key, InvoiceNumber, make this the second group level to
keep all three of each invoice together if there are more than one for the
same date or customer for instance. You don't need any headers or footers in
the parent report; if you are aggregating values to give an invoice total do
this in the subreport's footer.

You should then get three complete invoices per InvoiceNumber. You can force
a page break after each three by including a hidden unbound text box control,
txtCounter, in the detail section with a ControlSource of =1 and a RunningSum
property of 'Over All'. Add a page break control at the bottom of the detail
section and in the section's Format event procedure put:

Me.YourPageBreakControl.Visible = (Me.txtCounter Mod 3 = 0)

This will give you a blank page at the end of the report, so if you happen to
have a 'Page # of ##' text box in the page footer make its ControlSource
something like this to omit the blank page from the count:

="Page" & [Page] & " of " & [Pages]-1

Ken Sheridan
Stafford, England

How do you use this method to print an invoice 3 times on one sheet? I
cannot move everything into the detail section, as then there is no sorting
or grouping in effect; ie, it would only print out the first item in the
order.

You can either do as Fred suggests, or for a more flexible way of printing
multiple copies of a record do the following.

[quoted text clipped - 49 lines]