Conditional Page Break on Sub-Report?

[Meltdown]

Using Access 2007

Hi all,

I've just found out that page breaks don't work on sub-reports, has anyone ever figured out a work-around solution to this problem?

I have a Counter on the details section of a sub-report and I need the page to break at a certain number.

Regards
Melt

 

[vbaInet]

Are you chaning the visible property of the page break control in code? Tell us what the idea behind this is.

 

[Meltdown]

Hi vbaInet,

Yes, I've tried changing the visibility of the page-break on the sub-report, Access just ignores it, it only work on a main report.

I need to be able to break my sub-report based on my Counter number. The Access properties (Grouping, Keep Together etc ) are not giving me what the users need, the reports are always breaking in an inappropriate place.

Regards
Melt

 

[vbaInet]

Ok, the page "belongs" to (or is controlled by) the parent report. Putting a page break in your subreport will be fruitless. One way to do this would be to set the Force Page Break property in code.

Here's how:

    If counter > 10 Then
        ' 1 - stands for Before Section
        Me.Detail.ForceNewPage = 1
    Else
        ' 0 - None
        Me.Detail.ForceNewPage = 0
    End If

 

[Meltdown]

Hi vbaInet,

Thanks, but that won't work as it's just pushing a whole section or group forwards, leaving rivers of white space.

Let me give you an example of the problem I'm trying to solve on the sub-report.

"The sub has 20 line-items with sub-totals and totals on the page. Even when Keep Together is selected Access still pushes some details onto a second page when there isn't enough room for all the content. So what happens is the 'totals' end up being printed on the second page (effectively being orphaned from the 20 line-items on the report). If I can allow my user to push a couple of the line-items onto the second page, then the 'totals' won't look orphaned."

Regards
Melt

 

[vbaInet]

Why don't you move the totals to the parent report's and refer to the sum in the subreport?

Or just increase the height of that section so it all fits into one page?

It might be easier to fix if I could see the report.

 

[Meltdown]

Why don't you move the totals to the parent report's and refer to the sum in the subreport?

Or just increase the height of that section so it all fits into one page?

It might be easier to fix if I could see the report.

Hi vbaInet,

The example I gave above is just one of dozens that crop up on these reports, so I'm afraid any hard-coded solution in relation to moving controls or altering heights will not work.

The details on every report are custom created, every report is unique, so showing one report would not be of any help to you.

Thanks for your input.

Regards
Melt

 

[vbaInet]

There's another way of manipulating the records. Look into these three methods:

Me.NextRecord
Me.MoveLayout
Me.PrintSection

Like already mentioned, the creation of new pages is handled by the section that subreport control is in so you can also try Forcing a new page from there instead of via the subreport.

I would be more inclined to handle the totals in code if that's what all the problem is.