Obtaining and using info from the user just prior to running a rep

[Guest]

I print shipping labels using a command button with the following wizard
generated code:
Private Sub PrtLabBut1_Click()
On Error GoTo Err_PrtLabBut1_Click

Dim stDocName As String

stDocName = "ItemLabelsDYMO"
DoCmd.OpenReport stDocName, acPreview


Exit_PrtLabBut1_Click:
Exit Sub

Err_PrtLabBut1_Click:
MsgBox Err.Description
Resume Exit_PrtLabBut1_Click

End Sub


However, before the report runs I need to prompt the user (our shipper) to
enter the number of items he will be placing in each package. I need to
then take this number and divide it into the number of items on the order so
the report will print the correct number of labels. How can I do this? Do I
need a function? I understand how to write the expression, ([qty]/[number of
items per package] but don't know how to intergrate it with the open report
code nor do I know how to request the info (I think this is a variable) at
run time and then use it in the expression.

The label report is based the query that follows which uses a "Cartesian
Product" - I hope I am using the term correctly, which I will modify to
connect the "Num" table to link to NumberOfLabels instead of Qty as it is
currently.

Any help is greatly appreicated.


SELECT OrderLineItems.LineNumber, OrderHeader.OrderAutoNumber,
OrderHeader.CustomerName, IIf([CustomerPO] Is Not Null,"P.O. " &
[customerPO]) AS CustomerPO2, OrderHeader.Sidemark, IIf([Room] Is Not Null,"
- " & [Room]," " & [Rooom] & "- " & [Unit]) AS Room2, OrderLineItems.Item,
OrderLineItems.Qty, OrderLineItems.UOM, OrderLineItems.FaceWidth,
IIf([OrderLineItems.UOM]="Pair()",[OrderLineItems.Flength] & " (" &
[WESround] & " WES)",[OrderLineItems.FLength]) AS FLength2, Num.N,
[ProdperPkgCnt] AS [Enter No In Pkg]
FROM OrderHeader INNER JOIN (Num INNER JOIN OrderLineItems ON Num.N <=
OrderLineItems.Qty) ON OrderHeader.OrderAutoNumber =
OrderLineItems.OrderNumber
WHERE
(((OrderLineItems.LineNumber)=[Forms]![OrderEntry]![OrderLineItems].[Form]![LineNumberBox]))
ORDER BY Num.N;

Thanks so much

 

[Arvin Meyer [MVP]]

There is a Knowledge Base article that mostly addresses your question:

http://support.microsoft.com/kb/299024/en-us

You may have to use an input box, a dialog box, or another function to
determine the counts you want to print.