Page Breaks within Nested Subreports

I have attached a sample database to illustrate the issue that I am having. I have city reports nested within state reports. I want each city report to start on  a new page. Within the Report Header properties of each city sub-report, I have set Force New Page to 'Before Section'. Please provide assistance on how to have each city sub-report begin on a new page.

Please execute 'MainReportShell'.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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, only need one city subreport .
(When we see multiple tables, all with the same fields, ...this screams "un-normalized design")
Potential Un-normalaized tables.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.

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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jeffrey CoachmanMIS LiasonCommented:
Ron,...that is what I asked too...
newbie46Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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, 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.

newbie46Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Here is a simple sample db that illustrates how easy it is to get a page break after each section, 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")

newbie46Author Commented:
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.

Jeffrey CoachmanMIS LiasonCommented:
it can be done, ...bit it is more cumbersome,.

But again, this will work for a "Normalized" design
new sample attached
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.

Jeffrey CoachmanMIS LiasonCommented:
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...
newbie46Author Commented:
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.
newbie46Author Commented:
You mentioned "it can be done, ...bit it is more cumbersome"

I don't mind cumbersome, if it will provide the desired results.
Jeffrey CoachmanMIS LiasonCommented:
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, could print multiple "City" reports one at a time...
that will probably give you the effect you are after...

newbie46Author Commented:
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.
newbie46Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
I am still not a fan of using subreports if groupings alone will suffice.

I understand that you have a unique situation here, if this works for you , ...then roll with it...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.