Multiple labels to match quantity?

I have an Access db set up for label printing for one of our customers.  My label template is an Access report that pulls data from the database on to a 4 X 6 report template.  My question is: If I have a quantity of 4 of a particular product being shipped out, is it possible to print out 4 labels and have a text field down at the bottom that says "1 of 4", "2 of 4", etc.?
The table I'm pulling the info out of is called "tblProducts", and the quantity field is labeled "Qty".  
If anyone has any suggestions, I would greatly appreciate them!
jb7811Asked:
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.

jefftwilleyCommented:
a textbox with this as the control source will give you the page of pages.

="Page " & [Page] & " of " & [Pages]
0
jb7811Author Commented:
That's cool, but I need Access to see the quantity and then create 4 numbered pages.  Can Access do that?
0
jefftwilleyCommented:
I guess I'm confused. Your template report prints out what now...just 1 page? with 4 labels on it...and you need 1 label on 4 pages instead?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jb7811Author Commented:
That's exactly correct.
0
jefftwilleyCommented:
IS there currently any grouping for your template?
Will each label be exactly the same? only you want 1 of, 2 of etc...?


try clicking on the detail bar of your report. Then in properties, set the Force New Page to "After Selection".

See if that gets it.

J
0
jb7811Author Commented:
I don't believe there is grouping on the template, and yes, each label will be exactly the same.  I'll try what you suggested.  Thanks for hanging in there with me.
0
Jeffrey CoachmanMIS LiasonCommented:
jb7811,

A few Questions:

So your system already prints the correct number of labels and you only need help getting "Label 1 of 4" to print on each label? Correct?

Does your System print All the Labels for All the Products, at the same time?
(Or, do you have to "Run" your system for each Product?)

Does your table look something like this:
ProductID      LabelQty      ShipToCountry
100      5      UK
150      2      USA
275      1      Spain
120      5      France
166      6      Germany

Access standard Labels print 1 label for each record.

Can you explain how your system takes the above table, and generates the correct number of labels?

Jeff Coachman
0
Jeffrey CoachmanMIS LiasonCommented:
jb7811:

OK, I just read JeffW's post:
<Your template report prints out what now...just 1 page? with 4 labels on it...and you need 1 label on 4 pages instead?>
... and your Reply:
<That's exactly correct.>

… Ignore my post for now, let’s see what jefftwilley comes up with.

Jeff Coachman
0
Jeffrey CoachmanMIS LiasonCommented:
jb7811:

Can you post some data from your table here?

Or better yet, upload your database to:
www.ee-stuff.com

Jeff Coachman
0
jb7811Author Commented:
This is some data from the table I print labels from.

ID|PO|ISBN|Title|PubDate|Country|USPrice|CANPrice|Qty|Weight
6|123|978-0-9787320-5-9|Color Me Butterfly|2007|USA|22|37|11|11
7|555|978-0-9787320-5-9|Color Me Butterfly|2007|USA|23|32|4|8
8|1234|717298100076|Hallmark Keepsake Software|2007|USA|64|45|1|2

Currently I click a button on the switchboard that says "Print Packing Labels".  I am then prompted for the PO, which I key in, and then my report/label template pops up with all of the above info, except for ID, which is my primary key.  Only one label comes up at this point.  What I need to do is have whatever the number is in the Qty column to print that number of labels, or something comparable, and the labels need to be tagged "1 of 4", "3 of 9", whatever may be the case.  I realize that this may be a lot to ask of Access.  I've also uploaded a copy of the database.  You guys are great, thanks very much.
0
jb7811Author Commented:
https://filedb.experts-exchange.com/incoming/ee-stuff/3035-BluSky.zip

This is the link to the DB.  Please be kind to it.  It is a simple Access database in a ZIP file.
0
jefftwilleyCommented:
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
jefftwilleyCommented:
I created a temp table and a small form.
The form lets you choose your PO number and displays the quantity from the products table
I put a little code behind the form that populates the temp table.
I changed the control source of the report to the temp table.
I changed the switchboard so that it opens the form.

I figure you can make the form an the report all pretty like you want them.

Let me know if you need any more help.
J
0
Jeffrey CoachmanMIS LiasonCommented:
jb7811:,

OK I made one too!
:)

I used a loop to copy the record multiole times into a temp table.
Then based the report on the temp table.
I created a Menu so you can select the PO to print.

When you open the Report at the bottom of the label you should see:
    "Page X of Y"
... printed correctly (and you can navigate through all the labels to view and verify them)

https://filedb.experts-exchange.com/incoming/ee-stuff/3039-BluSkyJC.zip

Hope this helps as well

Jeff Coachman
0
Jeffrey CoachmanMIS LiasonCommented:
Ooops!

I swear Jeff. I did the exact same thing as you!

I will look to see how you made yours!
:)

Jeff coachman
0
jefftwilleyCommented:
lol...great minds
0
Jeffrey CoachmanMIS LiasonCommented:
OK JeffW,

Your system is a lot more streamlined than mine.
;)

But it's funny that we both went the extra step of creating the menu.
Even our form name is the same!

I have more comments though
;)

I tip my hat to you!

I thought you were sick?
You are supposed to be in bed resting.

Pehaps I should collapse my lung, maybe, I'll get smarter too!

Take care!
:)

Jeff Coachman
0
jb7811Author Commented:

Man, that's exactly what I needed.  Your Genius rank is well-deserved.  Many thanks from Tennessee.
0
jefftwilleyCommented:
JB,
Anytime...let us know if you run into any more problems.

JC,
I wouldn't recommend the collapsed lung thing...And I am resting until Monday when I go back to regular work. Too bad I couldn't make a living at this sitting at home in my pajamas drinking coffee!! :o)
J
0
Jeffrey CoachmanMIS LiasonCommented:
<Too bad I couldn't make a living at this sitting at home in my pajamas drinking coffee!! >

Me too!
:)
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.