Solved

Multiple labels to match quantity?

Posted on 2007-03-29
20
298 Views
Last Modified: 2013-11-28
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!
0
Comment
Question by:jb7811
  • 7
  • 7
  • 6
20 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18819528
a textbox with this as the control source will give you the page of pages.

="Page " & [Page] & " of " & [Pages]
0
 

Author Comment

by:jb7811
ID: 18819676
That's cool, but I need Access to see the quantity and then create 4 numbered pages.  Can Access do that?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18819726
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
 

Author Comment

by:jb7811
ID: 18819779
That's exactly correct.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18819854
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
 

Author Comment

by:jb7811
ID: 18820076
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18821287
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18821337
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18821416
jb7811:

Can you post some data from your table here?

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

Jeff Coachman
0
 

Author Comment

by:jb7811
ID: 18823201
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jb7811
ID: 18823216
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 250 total points
ID: 18824512
0
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 250 total points
ID: 18824538
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18824727
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18824737
Ooops!

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

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

Jeff coachman
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18824774
lol...great minds
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18824794
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
 

Author Comment

by:jb7811
ID: 18825285

Man, that's exactly what I needed.  Your Genius rank is well-deserved.  Many thanks from Tennessee.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18825353
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18825569
<Too bad I couldn't make a living at this sitting at home in my pajamas drinking coffee!! >

Me too!
:)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now