Microsoft Access Tips for Serious Users

Provided by Allen Browne, August 2002. Updated November 2006.

Print a Quantity of a Label

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).

An unreliable approach

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, since the events of the sections do not fire in this view.

A Better Solution

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.

1. Create a new table, containing just one field named CountID, of type Number (Long Integer). Mark the field as the primary key (toolbar icon). Save the table as tblCount.
2. Enter the records into this table manually, or use the function below to enter 1000 records instantly.
3. Create a query that contains both this table and the table containing your data. If you see any line joining the two tables, delete it. It is the lack of a join that gives you a record for each combination. This is known as a Cartesian Product.
4. Drag tblCount.CountID into the query's output grid. Use the Criteria row beneath this field to specify the number of labels. For example, if your table has a field named Quantity, enter:
      <= [Quantity]
   or if you always want 16 labels, enter:
      <= 16
5. Include the other fields you want, and save the query. Use it as the RecordSource for your label report.
6. Optional: To print "1 of 5" on the label, add a text box to the report, with this in its ControlSource:
      =[CountID] & " of " & [Quantity]
   Ensure the Name of this text box is different from your field names (e.g. it can't be named "CountID" or "Quantity"). To ensure the labels print in the correct order, include CountID in the report's Sorting And Grouping dialog.

That's it.

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:
   ? MakeData()

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).