Page Breaks within Nested Subreports

The Report Header won't do much good here. Try setting that option in the Page Header, or (if you have a Group section) before or after that group section. If you don't have a Group section, consider adding one and then set FNP in that Group Header or Footer.

1. Also provide a clear "graphical" example of the exact output you want, ...based on the data in the sample db.
3. I am confused by your report design,  why are there two State subreports?
4. Why are the State subreports subreports themselves and not directly in the main report?
5. Additionally, why are there Multiple "city" subreports?
You should have a basic one to may relationship, so if one State can have multiple Cities, ...you only need one city subreport .
(When we see multiple tables, all with the same fields, ...this screams "un-normalized design")
6. You do not have any relationships set up, so it is difficult to understand why this type of design is needed.
7. Page breaks are typically controlled by the Highest Report in the hierarchy, not the subreport(s), themselves.

While we appreciate the sample database, ...there is no context here that shows us why this type of report design is needed.

JeffCoachman

I'm confused with all  your tables.  How are you coming up with all the various temp data tables?  What's more, none of them make use of the CityID or StateID you created in the other tables.  I think you going about this the wrong way.  It seems to me that you really should only have one table for your temp data that consolidates all of these temp tables.  That way, you would only have a single report that doesn't make use of any subreports.

In addition, a better State ID to use would be the State abbreviation itself.  NY, CA, MO, etc.
Ron

Ron,...that is what I asked too...

Jeff,
Thanks for the feedback.  I was not able to upload the real database, so basically attempted to quickly display the nested  structure that is causing the issue. The reports contain different information and need to be set up as separate reports.

The reports actually retrieve data from multiple data sources in order to display current year data, previous year data and the difference between the 2 in a table format. Thus, the use of populating a temp table to store the data. Each report slices and dices the data differently by branch, specific program, etc. Overlooking the failed attempt to display how the database is really set up, please provide guidance as to forcing page breaks between the city reports.

The goal is to run 1 report having continous page numbering, thus the nesting of the reports. There are initial overview reports, followed by each state report and it's associated city reports.

This is still a confusing design... (both in the repeating tables aspect and in the reporting aspect)
And I am still not sure I agree with the reasoning ...

Again, without any real context here, ...it is difficult to get you a solution to your current design, or to propose a new design/methodology.

What I can say is that:
1. The current (un-normalized) design will cause you nothing but headaches going forward.
2. What you are asking for here (page breaks for each subreport), can be done quite easily in a normalized design, ...or with a "grouped" report.

JeffCoachman

Thanks, Jeff.

Let's forego the database structure for now.  Creating a grouping by city name and then setting Force New Page to before section within the city name header, within the nested sub-report should force a new page?
Please confirm whether this should force a new page. This does not appear to be working.

I am not able to alter the database now and repost, as we have lost power.

Here is a simple sample db that illustrates how easy it is to get a page break after each section, ...in a "grouped" report.
Then same thing can be done with subreports, ...but subreports add complexity, and you have to worry about things like linking fields, references, and not knowing what properties effect the main report and will affect the subreport.

In other words, don't use subreports if a grouped report will suffice,
(many times they can produces the same "presentation")

JeffCoachman
Database103.mdb

Thanks, Jeff. I will take a look at this when our power is restored. In my situation,  I think that nested reports will need to be used, but I will review my structure to see if it is possible to have a grouped report encompassing all of the data.

Please provide an example of where you are able to set page breaks with sub-reports nested within another sub-report.

Thanks.

it can be done, ...bit it is more cumbersome,.

But again, this will work for a "Normalized" design
new sample attached
Database103.mdb

Ron,...that is what I asked too...

I noticed that, Jeff, after I posted.  Strange though, because I refreshed the page right before I posted and your post did not show up.

Ron

Ron, no problem.
The more eyes on this issue, the better.

I don't see an easy way around the problem without a re-design at some level...

Thank you, Jeff, for posting the updated database. I see that you have set the Force New Page to 'After Section' within rptCitiesSub. If I had the time to recreate the actual database on my personal computer, because I cannot post the database (stored on a government computer) to this site, you would see why I need 4 separate (different) "city" reports and why a grouped report will not work in my situation. My tables are normalized, but that is not relevant to my question.

In that light, I will return to my original question using your posted database. If you added a 2nd rptEmployeesSubSub within rptCitiesSub, would you be able to force a new page for both rptEmployeesSubSub and the new 2nd rptEmployeesSubSub-2 report?

It seems as though the answer to this is No. Please confirm.

Thank you.

You mentioned "it can be done, ...bit it is more cumbersome"

I don't mind cumbersome, if it will provide the desired results.

sure,
You could try adding a manual page break after the subreport.

But again, Page breaks are mainly controlled by the main report, so this may not get you what you want.

If you have only a few cities, ...you could print multiple "City" reports one at a time...
that will probably give you the effect you are after...

JeffCoachman

Thanks, Jeff, for your suggestions.

I was able to solve the problem by creating additional groups (=1, =2, and =3). I moved each sub-report to its own group and stretched its size to 7 inches.  I set the Can Shrink property to No.

Then, I set Force New Page to Before Section within each group header.

This is correctly forcing page breaks between the 4 sub-reports and correctly maintaining the parent/child linkage between the state reports and their associated city reports. Please see the attached screenshot.
Nested-Subreport-Solution.docx

Actually, I didn't need to stretch each report to 7 inches and set Can Shrink to No. Simply setting Force New Page to Before Section within each group header (=1, =2, =3) solved the problem.