Need to print multiple pages based on number contained in one field

I have an access database with a linked table, a query, and a report.  The query is designed to pull out one unique record from the linked table based on the information entered by the user.  The single record has a field we'll call "number of pieces".  The report has the query data arranged in a readable format to print labels to affix to packages.  

I need to print multiple copies of the label based on the number of pieces on the order.  If the order has 4 pieces, then I need four labels.  I have a field on the label described as "Piece ____ of ____".  The ideal solution would have four labels print out with each lable showing Piece 1 of 4, Piece 2 of 4, Piece 3 of 4, Piece 4 of 4.

I'm not an access expert by any stretch of the imagination, but can figure out/learn new tricks as they are explained.

Thanks in advance for any help.
afreemaniiiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I need to print multiple copies of the label based on the number of pieces on the order.  If the order has 4 pieces, then I need four labels.  I have a field on the label described as "Piece ____ of ____".  The ideal solution would have four labels print out with each lable showing Piece 1 of 4, Piece 2 of 4, Piece 3 of 4, Piece 4 of 4.>>

  There are a few ways to do this:

1. Build a temp table from your original query and create multiple records for each of the original records, then base the report off the temp table.

2. Use the MoveLayout, NextRecord, and PrintSection properties of the report to print the number of copies you need of each label.

The latter is probably the simplest.  Place two text controls on your report.  The first unbound and the second bound to the label count that's required (the field in the underlying record that has the number of labels needed).

Call the first txtLabel and the second txtLabelCount. Then in the Detail OnPrint event, do this:

  txtLabel = "Piece " & PrintCount & " of  "
 If txtLabelCount = 0 then
   Me.NextRecord = True
   Me.MoveLayout = False
   Me.PrintSection = False
Else
  If PrintCount< txtLabelCount then
     Me.NextRecord = False
  End If
End If

JimD
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
afreemaniiiAuthor Commented:
Thank you very much!!  Your directions were spot on and made it super easy for me to use them.
0
afreemaniiiAuthor Commented:
JimD - Your solution works great on orders with more than one piece, but how can I make the label still print out "Piece 1 of 1" when there is only one piece?  
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
afreemaniii,

<<JimD - Your solution works great on orders with more than one piece, but how can I make the label still print out "Piece 1 of 1" when there is only one piece?  >>

  Not sure why your having a problem; code works fine here.  Did you cut and paste or re-type it?  If the latter double check the code.

JimD
0
afreemaniiiAuthor Commented:
I found it.  I had the layout wrong so it wasn't displaying properly.  Everything looks great now.  Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.