Solved Next Page (1 Viewer)

[Gismo]

Hi,

I have a report with a few sub reports
main report will always only have 1 record
the first sub report could have any number from zero to 50 records

the report design setup allows only for 6 records in the first sub report

if the main report only has 1 record, how do I go about to force a new page with the remainder of the records for the first sub report?

 

[Micron]

You want a page break after the one main record regardless of how many records the sub report has? Then you just insert a page break: design tab, controls section, find 'insert page break' - right after the controls that are for the main report record. At least, that works on a normal report. Note that you cannot see page breaks in report view, just print preview if I'm not mistaken.

 

[Gismo]

You want a page break after the one main record regardless of how many records the sub report has? Then you just insert a page break: design tab, controls section, find 'insert page break' - right after the controls that are for the main report record. At least, that works on a normal report. Note that you cannot see page breaks in report view, just print preview if I'm not mistaken.

I understand what you are saying with the page break, not sure it will work here
below, basically everything on this page is related to the 1 line record in the main report
everything in the yellow highlighted area is the sub report, which only displays 6 records at a time, then must repeat everything on the report as on the first page but the highlighted area must then continue with record number 7.

 

[plog]

main report will always only have 1 record

If that's true, you don't need a first sub-report, it can be your main report. First, use a query to combine your datasources for the existing main and sub1 reports. Essentially, it will look like the datasource for sub1 but fields for the data in your main datasource. Base your report off that new query and then add a grouping level for all the main record data and put all its fields in the group header. In the detail section use the data fields from what used to be sub1 (the detail section replaces sub1). In the group footer you can then use all your other existing sub reports.

 

[Micron]

OK, I didn't interpret your original post correctly and agree that the design you have is a barrier you don't seem to need - subject to correct interpretation now. I don't know what you're trying to achieve with the six records you mention. It's as if you're trying to break your sub report records into 6 record segments (or at least the first segment is 6; no idea what happens after that). So maybe we're still not on the right track.

 

[Gismo]

Hi All,,
Im back on this issue again.
my requirements changed
I have a main report that will always have only 1 record
A sub report could have multiple but the page design as required, may only contain 6 records
then the page needs to go to page 2 for remainder of records
I rewrote the main report query as advised abobe but now the report runs over 8 pages as the main report now has the 8 lines included which reflects in the sub report

please could you advise

 

[Gismo]

If that's true, you don't need a first sub-report, it can be your main report. First, use a query to combine your datasources for the existing main and sub1 reports. Essentially, it will look like the datasource for sub1 but fields for the data in your main datasource. Base your report off that new query and then add a grouping level for all the main record data and put all its fields in the group header. In the detail section use the data fields from what used to be sub1 (the detail section replaces sub1). In the group footer you can then use all your other existing sub reports.

How do i limit the report to only have 6 records from the main report and force a new page?

 

[Gismo]

maybe this could help to understand what my layout looks like
Sub 3 is my issue, only 6 lines per page, then force new page, but all the other sub reports then needs to be duplicated with same information
basically what i need is force a new page when sub report has >7 lines

 

[Minty]

I think you may need to put some form of VBA counter in the sub 3 on format event, then force a new page based on that.
Pretty sure I've seen that done somewhere, but I'm not sure how on how you would get all the other related subreports to repeat.

I'm wondering if you maybe need to pull the whole lot except sub 3 into one query with a load of repeating data and then make sub 3 work as you need to.
I love complicated reports

 

[Gismo]

I have tried a few methods
Had all related field in the same query but then I get 8 pages in this instance with 8 records
Repeating the other sub reports on the next page wasn't an issue
also tried coding the detail section of sub 3 with a pagebreak and a counter
#
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.brkMyPageBreak.Visible = (Me.txtCounter Mod 10 = 0)

End Sub#
but access did not recognize Me.BRKMyPageBreak
when I remove the records from the main query for sub 3, it does not know when to go to a next page either

 

[Minty]

Have a read of this thread https://www.access-programmers.co.uk/forums/threads/conditional-page-break-question.298148/
Might steer you in the right direction.

 

[Gismo]

Have a read of this thread https://www.access-programmers.co.uk/forums/threads/conditional-page-break-question.298148/
Might steer you in the right direction.

still no solution on my side
I just not sure how the handle this
If I replace sub 3 with controls in the main report, I can not restrict the records to 6 lines per page
if i keep sub 3 as a sub report, I dont know how to go to next page after 6 records

 

[Micron]

Can't recall ever trying this but can you put the main records and first 2 subs in the page header and subs 4 to 7 in the page footer and sub 3 in the detail section? Not sure that would be allowed, but I imagine it's the only way you'd get data to repeat on each page. How to break a main report header based on a sub seems to be the conundrum here. Even if that's possible, I don't see how you're going to repeat the other subs unless the are in headers/footers. Perhaps code in sub 3 needs to make a page break on main report visible when the sub 3 count gets to 6.

For me, getting a complicated report layout is not something I have a lot of luck on just pulling ideas from thin air and yours is a requirement I have not seen before. You might have to post a db copy to get a solution - if there is one.

 

[Gismo]

Can't recall ever trying this but can you put the main records and first 2 subs in the page header and subs 4 to 7 in the page footer and sub 3 in the detail section? Not sure that would be allowed, but I imagine it's the only way you'd get data to repeat on each page. How to break a main report header based on a sub seems to be the conundrum here. Even if that's possible, I don't see how you're going to repeat the other subs unless the are in headers/footers. Perhaps code in sub 3 needs to make a page break on main report visible when the sub 3 count gets to 6.

For me, getting a complicated report layout is not something I have a lot of luck on just pulling ideas from thin air and yours is a requirement I have not seen before. You might have to post a db copy to get a solution - if there is one.

I had all the sub is header and footer, they all repeat on next page, only sub 3 with the limit of 6 records per page is my issue

 

[Minty]

With the set up you describe you should be able to do what you require.
Have a read of this https://www.tek-tips.com/viewthread.cfm?qid=326172

Pay attention to where the page break is formatted.