Third SubReport Will Not Grow

[rubbernilly]

Apologies if this has been asked elsewhere. My search turned up no direct results.

I have a form with three subforms. The subforms are stacked vertically, but do not touch/overlap. They are the width of the main report, and they are 0" height. I want them to expand if there is data in that subreport dataset. The first 2 expand just fine, but the third will not.

I have confirmed all of the settings (can grow, etc.), and I know that the third report should be growing because if I reorder my stack of subreports (putting #3 between #1 and #2), then it is still the top 2 subreports that grow, and the third (which is now #2), will not. It seems Access will only "Grow" the first two.

I have seen discussions this as a sort of known bug, but I haven't seen a workaround besides embedding (in this case) my subreport #3 in subreport #2, so that Access is tricked into thinking it only has 2 subreports, and will expand things properly. I also understand there is a "VerticalAnchor" property in newer versions of Access which might or might not help.

Is there not a clean fix for this?

I have Access 2007, with Office 2007 SP2.

 

[dhookom]

You have to make sure the section of the report containing the subreports can grow. Also there is a maximum section height of about 22".

There is a workaround if you are running out of room in the detail section. You can create multiple detail sections. This is done by adding a lowest level of grouping on the primary or unique value in your report's record source. Display the new group's footer section and it is the same as a duplicate detail section. Try move subreport 3 into this new group footer.

Duane
Hook'D on Access
MS Access MVP

 

[rubbernilly]

That workaround works, thank you Duane!

However, I think I'm going to outgrow it pretty soon. If Access only supports growing 2 sub-reports per section, and I have the pseudo header, the detail and the pseudo footer, I'll bump into the limitation again, I think.

I am due to be upgraded from 2007 to 2016, so I will try to post back with whether the problem is addressed in that version.

Thanks!

 

[dhookom]

The sections are limited to 22" in all versions. You can add a lot of group sections with both header and footer sections. I am having trouble figuring out why you need some much height.

Duane
Hook'D on Access
MS Access MVP

 

[rubbernilly]

I'm sorry if I wasn't clear, Duane... It isn't the length of the section that limits me. If all three of my subreports expand, they would only total about 6-7 inches all together.

What is going wrong is that the third sub-report will not expand, even though its expansion might only take the Detail section from about 5" to maybe 7". If I reorder the subreports vertically (listing them 1, 3, 2), then the top two will expand (1 and 3) and the bottom one won't. What's more, if instead of giving the bottom subreport a height of 0" I give it something else -- say 2" -- it will populate with data, but it will not move down from the expansion of subreport #2. The second subreport will be sitting on top of the third. And, no, they don't touch or overlap as they sit on the page.

Moving the bottom-most report into the pseudo group footer like you suggested now fools Access into expanding that sub-report, but I'm about to have to add 2 more sub-reports to the main report. Instead of having to dink with more pseudo groups to get at their footers, I'm hoping that this glitch is corrected in Access 2016.

Lastly, I don't know if this has anything to do with it, but I have an architecture of a sub-report within a sub-report. So the heirarchy looks like this:

MainReport
...MainReport_SubReport1
......SubReport1_SubReport
...MainReport_SubReport2
......SubReport2_SubReport
...MainReport_SubReport3
......SubReport3_SubReport

Like I said, I think I'm getting Access 2016 on Monday, so I will report back if that alleviates the problem.

 

[rubbernilly]

So, Access 2016 installed and I can confirm that this corrected the problem, with one caveat.

All three sub-reports can now be back in the same section of the report, and they will all grow/expand and push lower objects down the page. I no longer need the pseudo group on my main report in order to put the third sub-report in the footer (and thereby trick Access into letting that sub-report grow).

However, now all of my sub-reports grow, regardless of whether they should or not... so, for instance, the data related to a record that might not have a presence in the 2nd sub-report will still show a 2nd sub-report, expanded but empty.

Did the test of when a sub-report should grow change?

 

[dhookom]

I just made a text report in 2016 and it all works as expected. Each subreports' Can Grow and Can Shrink are set to Yes. I am viewing in Report Preview, not Report View.

Duane
Hook'D on Access
MS Access MVP

 

[rubbernilly]

OK, further research and trial has demonstrated that it is a matter of the view that I am in. Apparently Report View will expand a 0"-height subreport, but Print Preview will not. I don't know if this is truly an Access 2016 behavior... I don't remember a 0-height subreport being expanded in a report in the previous versions of Access I had used (2007, 2003, 2000, and 97).

Still, for the purposes of this database, I will take it. I can work around the behavior as long as I know what triggers it.

Thanks for the help, Duane!