Print selected record multiple times

  • Thread starter Thread starter Blondyeee
  • Start date Start date
B

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!
 
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!
Click to expand...




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
 
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
 
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

fredg said:
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!
Click to expand...




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
Click to expand...
 
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.

Ken Sheridan said:
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 said:
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!
Click to expand...
Click to expand...
 
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

v_fas said:
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.

Ken Sheridan said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top