Printing variable number of report copies

[TD]

I have a report (actually an inventory label) that prints information about
inventory items including barcodes. The report is based on a query. The
query has one field in it that has a number value that represents how many
copies of the label need to be printed.
(The query is based on a purchase order and the number of items purchased on
the purchase order).

I have a form with a combo box on it listing all the open purchase order. I
can select the purchase order number that i want, it then runs a query on
the purchase order detail table and will print one label with the barcode on
it for each item on the purchase order. However, some items need to have
more than one label printed for them. That's why I have a field in the
query that represents how many copies of the label need to be printed.

How can I automate this process to print (for example:
1 label for item #1
2 labels for item #2
1 label for item #3
6 labels for item #4, etc....

Hope I have explained this well enough for someone to try to help me.

Thanks,
Terry

 

[Clifford Bass]

Hi Terry,

To do this create a table named "tblNumbers", with a primary key field
named "The_Number". Place into it the numbers 0 through 9. If you will ever
need to print more than 10 labels create this query named "qryNumbers 0-99":

SELECT [Tens].[The_Number]*10+[Ones].[The_Number] AS The_Number
FROM tblNumbers AS Tens, tblNumbers AS Ones;

That will allow for up to 100 labels. Then create a query like this,
which uses an item table for simplicity; you will adjust to use your query:

SELECT tblItems.Item_ID, tblItems.Item_Name, tblItems.Bar_Code,
tblItems.Number_of_Labels
FROM tblItems, tblNumbers
WHERE (((tblNumbers.The_Number)<[tblItems].[Number_of_Labels]))
ORDER BY tblItems.Item_ID;

Use that query as the source of your labels report.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi Terry,

Forgot to mention. The second query uses the tblNumbers which means at
most 10 copies of each label. If you need more than 10, use "qryNumbers
0-99" instead of "tblNumbers".

Clifford Bass