Access Diva Keep Main Report and Subreport Records Together

Keep Main Report/Subreport Records Together...

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.

Add tdPositionFlag and tdPageID to the RecordSource of your Form and add tdPageID to the Form.

Add a Command Button to your form and inset the code below in On_Click event procedure of your Command Button. 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.

Make sure to change to your Recordset and replace Me![txtTreatmentID] to whatever your Primary Key is.

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 procedure 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!

In the RecordSource for your Report and your Subreports add tdPageID.

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.

All done! Now 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...

The difficult I do immediately, the impossible takes a little bit longer.

Reports

Tips (Main)

Home

Print Full or Partial Sheet of Same Labels

Print One or More Labels for One or More Clients (Records)

Multiple copies of 1(one) Report using a Cartesian Product query

Multicolumn Report with Images

Filtering Reports

Ranking Reports Using Shapes

Print Constant Number of Lines (or add a Constant Number of Lines)

Custom Page Header

Send Report (or Labels) to Specific Printer

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
     End If
 
     If iPage < rs![tdPageID] Then 'Changed page number, continue numbering from 1 of next page
          IPosit = 1 'Reset
          iPage = rs![tdPageID] 'Set Page
     End If
 
     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.Update
     rs.MoveNext 'Move to next record
 
     IPosit = IPosit + 1 'Increment Position
 
Loop 'end of EOF do loop
 
rs.Close
Set db = Nothing
Else
MsgBox "Please look up a Treatment!", vbExclamation
End If
 
End Sub

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

Me.cmdResetPages.Caption = IIf(DLookup("MyPage", "qryPageOrder", "tdTreatmentID = " & [tdTreatmentID]) > 4, "CLICK ME", "IGNORE")