Report / Subreport's Report Footer totals printing alone on last page

[bschulze]

Hello all, i've given up and could use your help. Access 2013.

I have a Report which has two subreports in the detail section of the main report. The subreports both have a report footer section which contains totals. All records and totals are displaying correctly, my issue is that it's possible for the report to expand to a 2, 3, 4, etc... pages, but the subreport report footer to be the first item on a page. I would like to have at least one detail record on the page prior to the totals.

I've played with the following properties GrpKeepTogether, KeepTogether, NewRowOrCol and others but cant seem to figure it out. The two subreports can be moved into a group footer if that will help.

Appreciate any assistance.

 

[Micron]

You played with these properties on what, the main report or the second sub report? At first, I suspect your sub report totals need to be on the main report. Take a look at
Microsoft Access tips: Bring the total from a subreport back onto the main report
Otherwise, this might be one of those issues where you'll have to make a copy of the db available for analysis.

 

[bschulze]

I've played with a combination of these properties on the main report and both subreports. I'm not trying to bring totals from the subreport to the main report.

Imagine there being enough page space to display only 20 records per page. If 19 records are called on the subreport, the 19 records and 1 totals field (subreport report footer section) is displayed on page one. If 24 records are called, 20 will display on page one, 4 will display on page two alone with the totals. Both of these examples are perfectly acceptable. The issue I have is when there is 20 records, all 20 records are displayed on page 1 while the subreports report footer section containing the totals is pushed to page 2, all by itself, due to page space considerations.

I'm likely looking for some code to verify this scenario per page and if found move a record or records to the next page (possibly by making a spacer section visible?). Providing the DB is not an option, although this example is easily duplicated.

Let me know if further clarification is needed.

 

[Micron]

Providing the DB is not an option, although this example is easily duplicated.

If you cannot provide a copy, that's understandable. But with all due respect, I'm not about to create tables/queries/reports/subreports to attempt to duplicate your issue. Maybe what you need is to code on the report section format property (property details here) and (here) to limit the number of records and cause a page break at some number.

This code is from Experts Exchange and relies on a hidden textbox that sums overr group and (supposedly) forces a new page at 15, but I've never tried it. One thing I don't get is the Mod word.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If txtRowCount Mod 15 = 0 Then
   Detail.ForceNewPage = 2   ' after section
Else
   Detail.ForceNewPage = 0   ' none
End If
End Sub

or this idea (you'd modify so as to not simply cancel the printing, but move to a new page instead).
Note that the event doesn't fire in on report view - only print preview.
Good luck!