The difficult I do immediately, the impossible takes a little bit longer.
Keep Main Report/Subreport Records Together...
Private Sub cmdResetPages_Click()
Dim db As Database
Dim rs As Recordset
Dim iPage As Integer, IPosit As Integer, IMax As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT qryDraperies.* FROM qryDraperies WHERE qryDraperies.tdPositionFlag=True AND qryDraperies.tdTreatmentID = " & Me![txtTreatmentID])
If Not IsNull( Me.txtTreatmentID ) Then
IMax = 4 'Set Maximum number of records per page (You can change the IMax value to anything you like!)
rs.Requery 'Sorts to ensure first record is lowest value of page
rs.MoveFirst 'Ensures we start at first record
iPage = rs![tdPageID] 'Initial Page counter to value in first record
IPosit = 1 'forces position 1
Do Until rs.EOF 'loop through to End Of File
If IPosit > IMax Then 'Last one updated was at maximum or last position
IPosit = 1 'Reset
iPage = iPage + 1 'Increment Page
If iPage < rs![tdPageID] Then 'Changed page number, continue numbering from 1 of next page
IPosit = 1 'Reset
iPage = rs![tdPageID] 'Set Page
rs.Edit 'Enable updating
rs![tdPageID] = iPage 'Set new Page
rs![tdPositionFlag] = 0 'Set Position Flag to OFF so we know that record has been positioned
rs.MoveNext 'Move to next record
IPosit = IPosit + 1 'Increment Position
Loop 'end of EOF do loop
Set db = Nothing
MsgBox "Please look up a Treatment!", vbExclamation
Me.cmdResetPages.Caption = IIf(DLookup("MyPage", "qryPageOrder", "tdTreatmentID = " & [tdTreatmentID]) > 4, "CLICK ME", "IGNORE")
Usually, one can get the kind of report they want by simply using the Wizard and/or creating one from scratch. However, every once in a while you need one to do something special... like keeping the Main report and the Subreport's records together by page.
1. Put two fields in your table. In my case that would be tblTreatmentDetails so my field names are tdPageID (Number, Long), Default Value = 1; and tdPositionFlag (Yes/No), Default Value = True. (You can name your fields whatever you like, just remember to modify the code accordingly and be sure to set the Defaults.)
2. Add those fields to the RecordSource of your form where the records are being added. (I also added the field tdPageID to my form. Hot pink means it's not visible.)
3. Add a Command Button to your form. I named my button cmdResetPages but, again, you can name yours whatever you want. Just remember to modify the code accordingly. The code, seen below, is a modified version of the code found under Loop while Renumbering Two Columns. Insert the code in the Event Procedure of your Command Button.
Change to *YOUR* Recordset and replace Me![txtTreatmentID] to whatever your Primary Key is.
4. Now let's get the Command Button to tell us when we need to push records to the next page. (In the image above you will see the button says * IGNORE *. When it gets to the 5th record it will change to * CLICK ME *.) Create a Groups and Totals Query, see below, and name it qryPageOrder (or whatever you want but again you will need to modify the code accordingly.
a. In the On_Current event of the form place...
Now that we have the form set up to create our pages with our specific number of records, let's get the report set up!
5. In the RecordSource for your Report and your Subreports add tdPageID.
6. In the Main Report add tdPageID to the Group, Sort and Totals and Show the Header and the Footer. Put your Subreports in the tdPageID Header Section and a PageBreak in the tdPageID Footer Section.
a. When adding the Subreports...
Link Master Fields = tTreatmentID;tdPageID
Link Child Fields = tdTreamentID;tdPageID
tTreatmentID and tdTreatmentID are Primary Key/Foreign Key. Make sure you use the same a Primary Key/Foreign Key.
Finally, you can add some sample data and watch as the first four records are on Page 1, the second set on Page 2 and so on...