How to print multiple copies of a multiple records in a single report

[doshpra]

Hi!



I am beginner in MS Access and need assistance in printing multiple copies of different labels in a single report. I have a huge list of 2000 products and need to print out shipping labels of different products as per varying requirement in a single report. eg. if I need to print twenty nos. of labels of Product No. 5 (Prd 5) & six nos. of Product No. 44 (PRD 44) & thirty nos. of Product No. 200 (Prd 200) & so on depending on the requirement in single report and next time the quantity may vary as per the different requirement.

Is there a simple method wherein I can design a simple sheet / form/ query or simple VBA code (have no knowlegde of VBA!) and just put in the number of labels of required of a particular in a field box against the product name(s) and those particular products (names) are printed in a single report as per specified quantity?

Sample of the database is as follows:

ID ProductName
1 Prd 1
2 Prd 2
3 Prd 3
. .
. .
2000 Prd 2000

Any help in this regard will be highly appreciated.

[June7]

There is no simple and intrinsic method to accomplish that.

I do what you describe with VBA code that writes duplicate records to a 'temp' table. The specified quantity field could be used by the code to determine how many duplicate records.

[doshpra]

Thanks June7, Could you help me with the code to do print the required number of multiple records.

[June7]

Create field in Products table for the record count.

If you want to allow users to manually change the quantity but not overwrite the default, create another field in the table and populate it same as the first. Present the second field to user in a form to overwrite value. Use that second field in the procedure.

Create a table to serve as the temporary repository of label records. Adjust code for whatever fields you want.

Code like:

Sub btnPrintLabels_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
CurrentDb.Execute "DELETE FROM temptablename"
rs.Open "SELECT * FROM Products;", cn, adOpenStatic, adLockPessimistic
While Not rs.EOF
For i = 1 to rs!LabelCount 'or LabelCount2
CurrentDb.Execute "INSERT INTO temptablename(ProductName) VALUES('" & rs!ProductName & "'"
Next
rs.MoveNext
Wend
DoCmd.OpenReport "Labels"
CurrentDb.Execute "UPDATE Products SET LabelCount2=LabelCount;"
End Sub

Use of ADODB recordset will require VBA reference to Microsoft ActiveX Data Objects 2.8 Library.

I just remembered this question has been asked and answered. There is another code method that doesn't require temp table. Review https://www.accessforums.net/reports...ord-37535.html

Going to try and eliminate use of temp table for this process. I have variable count also so will be interesting.