Provided by Allen Browne, August 2002. Last updated: April 2010.
Need several labels for the same record? This tip works for a fixed number of labels (e.g. a whole sheet for each client), or a variable number (where the quantity is in a field).
A common suggestion is to toggle NextRecord (a runtime property of the report) in the Format event of the Detail section.
This approach works if the user previews/prints all pages of the report. It fails if only some pages are previewed/printed: the events for the intervening pages do not fire, so the results are inconsistent.
This approach also fails in the new Report view in Access 2007 and later, since the events of the sections do not fire in this view.
A simpler and code-free solution uses a query with a record for each label. To do this, you need a table containing a record from 1 to the largest number of labels you could ever need for any one record.
Here's the function that will enter 1000 records in the counter table. Paste it into a module. Then press Ctrl+G to open the Immediate window, and enter:
Function MakeData() 'Purpose: Create the records for a counter table. Dim db As Database 'Current database. Dim lng As Long 'Loop controller. Dim rs As DAO.Recordset 'Table to append to. Const conMaxRecords As Long = 1000 'Number of records you want. Set db = DBEngine(0)(0) Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly) With rs For lng = 1 To conMaxRecords .AddNew !CountID = lng .Update Next End With rs.Close Set rs = Nothing Set db = Nothing MakeData = "Records created." End Function
(The function requires the DAO library: more information).
|Home||Index of tips||Top|