Sub report in footer section to be printed (continued) on next page

[Grisbi]

Hi, please forgive me if this has been answered previously but I looked for that kind of stuff and didn’t find any solution in the pages I’ve found, or I didn’t understand what I’ve read! (I speak French or Italian better than English and I’m not a real programmer).
Here is my situation (hope it will be cleared enough!): I am producing an invoice report to be printed on a pre printed form with locations for invoice items and methods of payment. In case the invoice items and/or methods of payments needs more than one page, they must be printed on the next invoice page (same locations of course).
So, within a main report I used a sub report for the invoice items and another sub report for the methods of payment. The sub report for the invoice items is located in the detail section of the main report and run like it should: i.e. when the number of rows is over 20, it continues on next page of the report (I used an “If Me.txtCounter Mod 20 = 0 Then Me.Detail.ForceNewPage = 2” statement on format event).
The problem come with the sub report for methods of payment which is located in the main report footer section. I would like to print the first 8 rows on first page and then, if needed, the next 8 rows should be printed on the second page and so on… I tried to work with the same approach used for the invoices items but that section shows only the first 8 rows on each pages of the report.
Any help would be very much appreciated. Thanks in advance.

 

[lameid]

I don't know if this will work or not. Sometimes the timing of the way things work inside Access cause things not to work but it is the only thing coming to mind at the moment.

If you had some sort of field that would increment for each record in the sub-report's recourdsource, you could use an expression like the following...

Page: (RecordNumber + 7) \ 8

That will tell you which page the record should go on. Note the backslash (\) as opposed to the forward slash (/). This is integer division in Access.

Then you could use the reports page property as one of your master fields and page as your child field. It should work at least in theory. You may have to have the query of the subreport explicitly reference the page property of the main report as criteria.

 

[lameid]

Hmmm... Just occured to me that the code of the subreport could reference the page property of the parent and then you could use similar arithmetic on the record count to either set the visible proeprty of the detail section to true or false (or at least the controls in it).

I have seen the sequence question asked here before. Ideally you will not have to much trouble finding it.

For what it is worth, your English is much better than my French.

 

[Grisbi]

Lameid, thank you very much for your response and advises, I will work on that and tell you regarding. À bientôt!

 

[Grisbi]

Hi Lameid, as you suggest, I get the sequence of the records and number of the page the records should go on, in the subreport's recordsource.
Now you said “then use the reports page property as one of your master fields and page as your child field” excuse my ignorance, but how you do that? I tried a lot of things, but for sure, I should have missed something…

 

[lameid]

Right click the sub report control, select properties, Enter [PAGE] for the master field and the appropriate calculated field from the sub report as the child field... Master and Child fields are kind of like a join for main and sub forms/reports.

 

[Grisbi]

Okay, I was trying to do it trough the linking window (didn’t work because [page] doesn’t exist in main report’s recordsource). Anyhow, the point is now that only the ninth record is printed on the second page of the report/subreport. The others are still those already printed on the first page(?!)
By the way, I’ve just realized that in case the sub report items (methods of payment) needs one more page than the main report (invoices items), this solution couldn’t work, right?
Is it possible to print a report/subreport directly from a recordset (I have some experience on working with recordsets but none in relation to report/printer). Thank you for your help and patience.

 

[lameid]

I know you can set a form's record source to a recordset; although I've never had reason to do it... It would seem like it should work but I recently read someone post that it is a limitation of reports to not be able to set the recordsource equal to to a recordset.

You are right. If you have more than 16 items and the main report is 2 pages long, you will never see sub report itmes 17 + with this method.

You could of course consider putting the sub report at the end of the report instead but then it would not be in the page footer.

Maybe if you set the a global variable to the page number on the on format even of one of the controls in the detail section. Then you could force new page somewhere and use that value to calculate which values to show on the last page with the sub report... This way you would have two sub-reports in your report. My assumption is that this will give you the last page that detail is on. If it does not work, hopefully someone will have another idea.

It is odd that only the 9th record is shown though. I bet it has to do with timing. Try the other way with out linking the master and child fields and grabbing the page number as criteria in the query that is the recordsource of the subreport.

 

[Grisbi]

Hi lameid. Here is some good news. Grabbing the page number as criteria within the query for the subreport produce no change at all… BUT following your assumption that the problem had to do with timing, I first tried to use a subquery to get the numbers on fly to use as criteria but it didn’t not work either, then I filled the table source with the records sequence and pages number and that way, finally... IT GOES!
Now I get that last trouble with having more pages for the footer section (sureport) than for the main report. Forgive me, but I didn’t understand what you suggest to handle that case (“This way you would have two sub-reports in your report”). Thank again for your most valuable help.

 

[lameid]

Basically you need another sub report for the items that won't fit in the footer. I was suggesting trying to find the last page with detail on it and forcing a page on a section in order to get the sub report to start on the next page.

 

[Grisbi]

Hi, for those who could be interested in the situation exposed in this post, here is a solution for enforcing a break page when a sub report filtered on main page property needs more pages than for main’s sections.
In addition to the steps suggested above by lameid for grabbing the page number as criteria for the subreport’s records, and before to send the report to printer, we need to determine if the pages number for sub’s items are greater than what is required for the main’s items, or not. If it is, we retrieve from a dedicated function the last ID record to print on the main detail section. If not, we set that function to 0.
Then we can pass the ID to the report with OpenArgs property.
In the main report detail section we need a break page control and in the onformat event we have:
[MyBreak].Visible = (Me!IDItem = CInt(Me.OpenArgs))
Of course, none of the ID in the main report’s recordsource should be equal to 0 for the case we don’t need to print the break page (which is a common situation).
Thanks again to lameid for his lighting.
Hervé