I do something like this when I receive and order into Inventory. I want to print a label for each item I have received. In most cases I have more than one of each item so I need to use the units_received of each item in order to define how many labels. So one Recordset gets the products and the coresponding units_received. For Each product loop assign the value of the units_received to a variable that conditions a second loop. For each pass through the loop, add whatever values you need on the label to a temporary table. Once both loops have completed, close the recordsets, destroy the currentdb Object, and call the Report that will print the lables. Make sure that you have emptied the Temp table before you start (or after, I suppose).
Something like this might work
Set dbs = CurrentDb
strSql = "SELECT units_ordered, prod_code, trans_no FROM tblTransaction " _
& "WHERE po_no = " & Me.po_no & ";"
Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)
If rst.recordCount > 0 Then
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
i = 1
ordered = rst!units_ordered
For i = 1 To ordered
DoCmd.RunSQL ("INSERT INTO tblTempLables (prod_code, trans_no) VALUES ('" _
& rst!prod_code & "', " & rst!trans_no & ");")
Next
rst.MoveNext
Loop
End If
MsgBox "The Lables have been created. Opening Lables Report in Print Preview" _
& ".", , "Lables Created"
stDocName = "Labels qryLables_forReq"
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.RunSQL ("DELETE * from tblTempLables;")
DoCmd.SetWarnings True
End If
