You might be tempted to use VBA in the various report event handlers, but even if this might work, from my experience that would only lead to headaches trying to get everything to format properly. Instead, I recommend creating a new reporting table with a page number on each row. Source the main report from the sorted page number list and bind a multi-column subreport via the page number field. Populate the reporting table with a simple VBA procedure that correctly paginates the rows according to your scheme.
First the reporting table (add constraints as you find necessary):
CREATE TABLE SubReportTable (PageNum LONG, PageOrdinal LONG, _
Ordinal LONG, LastPage BIT, [Date of Service] DATE, [Location] TEXT)
On the SubReport:
- Set RecordSoucre property:
SubReportTable (alternatively specify a query that sorts on the desired fields)
- Set Number of Columns to 4 along with other column settings (padding, direction, etc.).
- Set CanGrow property to
No on Detail section and other controls as appropriate.
- Resize columns and detail section to properly fit all columns on page. (This will likely require going back and forth between print preview and design on the Main Form.)
On the main report, set the following properties:
- RecordSource property:
SELECT SubReportTable.PageNum FROM SubReportTable WHERE (((SubReportTable.LastPage)=False)) GROUP BY SubReportTable.PageNum ORDER BY SubReportTable.PageNum
- Detail section property Force New Page to
After Section.
- SubReport object's Link Master Fields and Link Child Fields both to
PageNum
- Resize SubReport object to fit all columns properly.
Duplicate the behavior on the Main Report on a separate "Last Page" report. Set this report to select the proper subset of records based on the pagination data in the reporting table (i.e. LastPage = True). Depending on how different the last page with 16 records is formatted, it might also require creating a separate SubReport just for the 16 records, but you might get away with using the same SubReport as the main report... that'll be your problem to determine.
- RecordSource property:
SELECT SubReportTable.PageNum FROM SubReportTable WHERE ((SubReportTable.LastPage = True)) GROUP BY SubReportTable.PageNum ORDER BY SubReportTable.PageNum
Finally some code to populate the reporting table. You can either run this procedure directly from the VBA Immediate Window, or put it in some button's click event handler. The pagination logic can be tweaked to get the right amount of records on the last page.
Public Sub PrepareSubReporTable()
On Error GoTo Catch_PrepareSubReporTable
Dim db As Database
Dim rs As Recordset2
Dim rows As Long, pgs24 As Long, rowsLast24 As Long, rows16 As Long
Dim i As Long, p As Long, pi As Long
Set db = CurrentDb
db.Execute "DELETE * FROM [SubReportTable]", dbFailOnError
db.Execute _
"INSERT INTO SubReportTable ( PageNum, PageOrdinal, Ordinal, LastPage, [Date of Service], [Location] )" & _
" SELECT Null AS PageNum, Null AS PageOrdinal, Null AS Ordinal, False as LastPage," & _
" [Data].[Date of Service], [Data].[Location]" & _
" FROM [Data]" & _
" ORDER BY [Data].[Date of Service], [Data].[Location];", _
dbFailOnError
rows = db.OpenRecordset("SELECT Count(*) FROM SubReportTable").Fields(0)
pgs24 = rows \ 24
rows16 = rows - 24 * pgs24
If rows16 > 16 Then
rowsLast24 = rows16
pgs24 = pgs24 + 1
rows16 = 0
Else
rowsLast24 = 24
End If
Set rs = db.OpenRecordset( _
"SELECT * FROM SubReportTable" & _
" ORDER BY [Date of Service], [Location];")
i = 0
Do Until rs.EOF
p = i \ 24 + 1
rs.Edit
rs![PageNum] = p
If p > pgs24 Then
rs![lastPage] = True
pi = (i - pgs24 * 24) Mod 16 + 1
Else
pi = i Mod 24 + 1
End If
rs![PageOrdinal] = pi
i = i + 1
rs![Ordinal] = i
rs.Update
rs.MoveNext
Loop
rs.Close
Exit Sub
Catch_PrepareSubReporTable:
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly Or vbExclamation, "Error in PrepareSubReporTable"
End Sub
Now generate the main report and the last-page report, either manually or in VBA code somewhere.
Note: I used the field name PageNum instead of Page because that seemed to cause problems with a SubReport binding during print preview... probably because Page is the name of an existing variable / function for reports.
