Would be more helpful to attach actual db or Excel or build table in the post. But I made my own table and records to test.
The example PDF shows 4 labels per sheet, not 3.
Why is label identified as C#1? Is that the ListOrder field?
Here is a tested procedure (thanks to Robyn for the ideas). Table has 3 fields - ListOrder, Address, PrintOrder. The ListOrder can be an autonumber field or your ListOrder as shown in image. PrintOrder is populated by code. Report has Sorting set on PrintOrder. It works for any number of labels per page without having to create dummy records.
Code:
Sub SetPrintOrder()
Dim rs As DAO.Recordset
Dim labelsPerSheet As Integer, countA As Integer, countRecs As Integer, countPages As Integer, x As Integer, z As Integer
' Get all labels
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Labels ORDER BY ListOrder")
labelsPerSheet = 3
rs.MoveFirst
countRecs = rs.recordCount
countPages = Int(countRecs / labelsPerSheet) + IIf(countRecs Mod labelsPerSheet = 0, 0, 1)
' Loop through the recordset populating the PrintOrder field
For x = 1 To labelsPerSheet
countA = x
For z = 1 To countPages
If Not rs.EOF Then
If countA <= countRecs Then
rs.Edit
rs!PrintOrder = countA
rs.Update
rs.MoveNext
countA = countA + labelsPerSheet
End If
End If
Next
Next
rs.Close
Set rs = Nothing
End Sub
Using the actual data table will cause conflicts if this is a multi-user db. If this is a multi-user db, should split the db and use a temp table in frontend to temporarily save records for this report output. Code would have to be modified to either open a second recordset on the temp table and add records to it or run INSERT sql action.