print multiple copies of records (mailing labels) based on a formu

[Guest]

I am setting up a Purchase Order database that stores part numbers in an
order form. Depending on the QTY ordered for each part number I need to
print a certain number of mailing labels with that part number information on
it.

For example, an order has
1) QTY = 300 of Part A
2) QTY = 200 of Part B

Depending on the case packs (information stored in the part number table), I
need to print out a certain number of mailing labels. In this case it may be
600 labels for Part A and 500 Labels for Part B.

Any suggestions?

 

[Graham Mandeno]

Hi WC

A report has a NextRecord property which is normally set to True, but you
can request that the current record be reprinted by setting it to False.

So, here's the trick: Add a textbox bound to the QTY field to the detail
section of your report. (If you don't want to print QTY then set its
Visible property to False). Now add the following code to your report's
module:

============================================
Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub
============================================

What this does is:

1. Reset iCopiesPrinted to 0 if this is the first copy of this label

2. Increment iCopiesPrinted for the current label

3. If the required number of copies (QTY) have not yet been printed then
suppress moving to the next record.

4. Finally, if we are starting a new page then reduce iCopiesPrinted by one.
This is because the Format event happens twice at a page break - once to
ascertain that the section won't fit on the old page and again to format it
for the new page.

 

[Guest]

Worked like a charm. Thanks

Hi WC

A report has a NextRecord property which is normally set to True, but you
can request that the current record be reprinted by setting it to False.

So, here's the trick: Add a textbox bound to the QTY field to the detail
section of your report. (If you don't want to print QTY then set its
Visible property to False). Now add the following code to your report's
module:

============================================
Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub
============================================

What this does is:

1. Reset iCopiesPrinted to 0 if this is the first copy of this label

2. Increment iCopiesPrinted for the current label

3. If the required number of copies (QTY) have not yet been printed then
suppress moving to the next record.

4. Finally, if we are starting a new page then reduce iCopiesPrinted by one.
This is because the Format event happens twice at a page break - once to
ascertain that the section won't fit on the old page and again to format it
for the new page.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am setting up a Purchase Order database that stores part numbers in an
order form. Depending on the QTY ordered for each part number I need to
print a certain number of mailing labels with that part number information
on
it.

For example, an order has
1) QTY = 300 of Part A
2) QTY = 200 of Part B

Depending on the case packs (information stored in the part number table),
I
need to print out a certain number of mailing labels. In this case it may
be
600 labels for Part A and 500 Labels for Part B.

Any suggestions?

 

[Guest]

I spoke too soon. There is no end to the report. The last label has no end.
Do I need to add anything in the Module to achieve an end?

Hi WC

A report has a NextRecord property which is normally set to True, but you
can request that the current record be reprinted by setting it to False.

So, here's the trick: Add a textbox bound to the QTY field to the detail
section of your report. (If you don't want to print QTY then set its
Visible property to False). Now add the following code to your report's
module:

============================================
Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub
============================================

What this does is:

1. Reset iCopiesPrinted to 0 if this is the first copy of this label

2. Increment iCopiesPrinted for the current label

3. If the required number of copies (QTY) have not yet been printed then
suppress moving to the next record.

4. Finally, if we are starting a new page then reduce iCopiesPrinted by one.
This is because the Format event happens twice at a page break - once to
ascertain that the section won't fit on the old page and again to format it
for the new page.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am setting up a Purchase Order database that stores part numbers in an
order form. Depending on the QTY ordered for each part number I need to
print a certain number of mailing labels with that part number information
on
it.

For example, an order has
1) QTY = 300 of Part A
2) QTY = 200 of Part B

Depending on the case packs (information stored in the part number table),
I
need to print out a certain number of mailing labels. In this case it may
be
600 labels for Part A and 500 Labels for Part B.

Any suggestions?

 

[Graham Mandeno]

That's very odd. It works for me!

It might have something to do with decrementing the counter at the end of
the page, perhaps combined with a situation where the last label is either
the last on a page or the first on a new page, or something...

Anyway, I've had a play around and have discovered that it actually works
better to use the Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static iCopiesPrinted As Integer
If PrintCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

For some reason I don't exactly recall, this didn't work in an old version
of Access (97 and before I think) but it's OK now.

Note that iCopiesPrinted is now a Static variable declared in the event
procedure, so you no longer need it declared at module level, and you can
also delete the Report_Page procedure.

Let me know how you get on.

 

[Guest]

It's working now. thanks for your help

That's very odd. It works for me!

It might have something to do with decrementing the counter at the end of
the page, perhaps combined with a situation where the last label is either
the last on a page or the first on a new page, or something...

Anyway, I've had a play around and have discovered that it actually works
better to use the Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static iCopiesPrinted As Integer
If PrintCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

For some reason I don't exactly recall, this didn't work in an old version
of Access (97 and before I think) but it's OK now.

Note that iCopiesPrinted is now a Static variable declared in the event
procedure, so you no longer need it declared at module level, and you can
also delete the Report_Page procedure.

Let me know how you get on.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I spoke too soon. There is no end to the report. The last label has no
end.
Do I need to add anything in the Module to achieve an end?

 

[Guest]

Hello,
I am a manufacturer looking to print work tickets that reflect the quantity
of items on an order. For example if an order lists 3 pillows and 2
bedskirts, I need to produce 5 tickets. The code created by Graham in this
post works great;

--
Lele

Hi WC

A report has a NextRecord property which is normally set to True, but you
can request that the current record be reprinted by setting it to False.

So, here's the trick: Add a textbox bound to the QTY field to the detail
section of your report. (If you don't want to print QTY then set its
Visible property to False). Now add the following code to your report's
module:

============================================
Dim iCopiesPrinted As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then iCopiesPrinted = 0
iCopiesPrinted = iCopiesPrinted + 1
If iCopiesPrinted < QTY Then Me.NextRecord = False
End Sub

Private Sub Report_Page()
iCopiesPrinted = iCopiesPrinted - 1
End Sub
============================================

What this does is:

1. Reset iCopiesPrinted to 0 if this is the first copy of this label

2. Increment iCopiesPrinted for the current label

3. If the required number of copies (QTY) have not yet been printed then
suppress moving to the next record.

4. Finally, if we are starting a new page then reduce iCopiesPrinted by one.
This is because the Format event happens twice at a page break - once to
ascertain that the section won't fit on the old page and again to format it
for the new page.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am setting up a Purchase Order database that stores part numbers in an
order form. Depending on the QTY ordered for each part number I need to
print a certain number of mailing labels with that part number information
on
it.

For example, an order has
1) QTY = 300 of Part A
2) QTY = 200 of Part B

Depending on the case packs (information stored in the part number table),
I
need to print out a certain number of mailing labels. In this case it may
be
600 labels for Part A and 500 Labels for Part B.

Any suggestions?