Best way to create dynamic shipping labels?

  • Thread starter Thread starter FT1973MDB
  • Start date Start date
F

FT1973MDB

I created a product / order database that they recently wanted to add a
shipping module to.

If we are shipping CDs, and you can fit 30 CDs in a box, I want the thing to
create a shipping label for each set of 30, and one label for any remaining
CDs in the last box.

So something like... (I will speak in Semi-SQL)
WHILE [ShipQty] is greater than 30 and more than 0
DO CMD Create shipping label;
[ShipQty]=([ShipQty]-30);
LOOP
DO CMD Create last shipping label
END

....Except I'm not quite sure what to say or where to put it, but I'm kind of
sure I need to loop... I was at first thinking writing the loop in a query
and then output via a "report" designed to fit on a label?? It's the end of
the week and my brain hurts - any help is appreciated.
 
I'm assuming that you have a field somewhere that has the quantity value. I'm
not sure how your tables are defined, but once you have that quantity, then
you could calculate the # of labels like so:

dim labelCount as integer

labelCount = quantity / 30
if quantity Mod 30 > 0 then ' gives remainder of quantity/30
labelCount = labelCount + 1
end if

that gives you the # of labels you need. I thought there was a way of
specifying the # of copies when printing a report via VBA code, but can't
remember how to do it. This would work though:

if labelCount > 0 then
for i = 1 to labelCount
docmd.openreport "reportName"
next i
end if

I'm sure there's a simple way to just print it once and specify the# of
copies.
 
I'm assuming that you have a field somewhere that has the quantity value.I'm
not sure how your tables are defined, but once you have that quantity, then
you could calculate the # of labels like so:

dim labelCount as integer

labelCount = quantity / 30
if quantity Mod 30 > 0 then     ' gives remainder of quantity/30
   labelCount = labelCount + 1
end if

that gives you the # of labels you need. I thought there was a way of
specifying the # of copies when printing a report via VBA code, but can't
remember how to do it. This would work though:

if labelCount > 0 then
   for i = 1 to labelCount
      docmd.openreport "reportName"
   next i
end if
Click to expand...
I'm sure there's a simple way to just print it once and specify the# of
copies.
Click to expand...

Public Sub PrintReport(ByVal strReportName As String, ByVal intCopies
As Integer)
DoCmd.OpenReport strReportName, acViewPreview
DoCmd.PrintOut acPrintAll, , , acHigh, intCopies, True
End Sub

The way I figured to do this would be to do the full sheets first.
Integer divide the number of labels by the number of labels on the
label page (30 for the 5160 style labels). Then print that number of
copies...

intReportCopies = intLabelsToPrint\intLabelsPerPage

Then join the query that has the single record to a table of numbers
from 1 to say, 29.

set the criteria to be less than or equal to the number of remaining
copies (intLabelsToPrint Mod intLabelsPerPage).

Then print that report.
 
Back
Top