Access report "Overflow" error on export to HTML (PDF works fine)

[ramendog]

I need to create HTML export of the report "rpt2024_Tst_summary_8_7_25" in the attached db. The export fails at about page 100 with the "Overflow" error. It is not specific to data in the table at that particular place in the report because I removed the data at the page of the overflow error and the error persisted. It seems to be related to the number of records being generated for the particular "Tst Num" in the report detail section when the error occurs. "Tst Num" with many (hundreds) of records seems to be where the export fails but I don't understand why or how to fix this issue.

The reason I need to utilize the HTML export is to generate web pages with tagging for use in a screen reader.

Any help or leads is greatly appreciated!

 

[Gasman]

It fell over for me on Page 138.
It also created individual pages.

THere are PDF to HTML converters, but that doe not have you Next/Previous links.

See if any solutions here

You might need to break it down to files of 100 pages at a time?

By the looks of it, only MP and AS are missing?

 

[ramendog]

Thanks, I did try googling various solutions but this is an odd issue and I did not find any relevant results. I already have a script that converts the Access HTML output and reformats it, consolidating the report pages. There are other reports with many more pages that work properly, so I suspect it is the number of records in the particular detail section that is triggering this issue.

 

[ramendog]

I also posted this question on accessforums, but I'm unable to post the link as the anti-spam tools are preventing a URL posting.

 

[isladogs]

The thread at AccessForums is at https://www.accessforums.net/showthread.php?t=91059

 

[xavier.batlle]

Hi ramendog,
After some tests, I noticed that the problem arises when the subreport has more records that fits on a page (I suspect).
For example: Kentucky: KY0- Number of 2024: 125
I've changed the main report recordsource to list only the the states where the subreport has 110 or less records with no issues.

 

[Gasman]

Crosspost replies reckoned on GA1 which has 160 records?
Not sure how the report is limiting to 20 records a page myself.

 

[ramendog]

These are very helpful hints, I am digging to see why the number of records might be causing this issue. The report was originally designed to export as a PDF so the records per page are (as far as I recall) an artifact of the page format and section height.

 

[isladogs]

Just for info, I tried adjusting the heights of the different sections to see if that had any effect
First of all I tried removing all the empty space thinking that would help and the overflow occurred sooner (page 107) rather than 138.

So I restored the original spacing & removed the unused report header & footer sections. This time the overflow occurred at page 152

Next I removed the two unused grouping footer sections but it overflowed at page 147. Restored both footers
Then I changed the grouping settings to Do not keep / group together on one page. Overflowed at page 160. Progress!

So whilst I don't have a solution for you, it may be that tweaking the layout will allow the report to complete if you play around some more

 

[CJ_London]

total number of pages is 437 -see attached

I did this per my suggestion on the other thread of simply removing the subreport, adding the other table to the recordsource and copy/pasting the controls from the subreport to the main report.

The only difference I can see from a brief review is the list of geocodes needs sorting which in the original appears to be Jurisdiction name

I leave to the OP to verify whether it does what is required

 

[ramendog]

total number of pages is 437 -see attached

I did this per my suggestion on the other thread of simply removing the subreport, adding the other table to the recordsource and copy/pasting the controls from the subreport to the main report.

The only difference I can see from a brief review is the list of geocodes needs sorting which in the original appears to be Jurisdiction name

I leave to the OP to verify whether it does what is required

This looks very promising, thank you. Let me work out how to apply these changes to the actual reports and test!

 

[CJ_London]

See the example db I attached

 

[ramendog]

See the example db I attached

THANK YOU, I think could be a viable solution! As you mentioned the records are no longer sorted by jurisdiction name. In the actual db, the records are pulled from a query. If I change the sort options in that query (to sort by TST Num and JURISDICTION NAME Ascending) the sort looks good in the query results but is not honored in the report, and trying to add the JURISDICTION NAME field to the sort and group properties of the report results in a "Invalid Sorting and Grouping" error. Do you have any suggestions about an approach to take to sort the records in the detail section?

The other issue I face is that I have a second report with the same "Overflow" error, but this has six sub reports in the main report detail section and each of these rely on a footer within the sub report to sum values from the details section in a "Total" field. Do you think this same approach is possible with this additional complexity?

 

[CJ_London]

Reports ignore the sorting in the recordsource- you need to do the sorting in the report

 

[ramendog]

Reports ignore the sorting in the recordsource- you need to do the sorting in the report

Would I need to somehow add the jurisdiction field to the report record source? Could I do it in the OnFormat property of the Details section? I'm trying various approaches but I don't really know what could work versus what is a dead end.

 

[CJ_London]

No - it’s already there - 2nd column if I remember correctly (I’m on my phone). In report design view on the ribbon you should see grouping and sorting options

 

[ramendog]

No - it’s already there - 2nd column if I remember correctly (I’m on my phone). In report design view on the ribbon you should see grouping and sorting options

Thanks, I was getting an "Invalid Sorting and Grouping" error at first but I tried it again and it worked.

 

[CJ_London]

just got back - this is what you should have - in green

 

[CJ_London]

The other issue I face is that I have a second report with the same "Overflow" error, but this has six sub reports in the main report detail section and each of these rely on a footer within the sub report to sum values from the details section in a "Total" field. Do you think this same approach is possible with this additional complexity?

without knowing what you have or what it looks like, possibly, you'll have to try it but I think the issue is with outputting to html - the use of an 'oversized' subreport causes the issue. So if you can avoid subreports (or at least those that potentially can 'oversize' by combining into the main recordsource it may work. You can try the things Colin suggested - might get you over the line. Or go via word. There are other ways to sum values, using dsum for example.

 

[ramendog]

without knowing what you have or what it looks like, possibly, you'll have to try it but I think the issue is with outputting to html - the use of an 'oversized' subreport causes the issue. So if you can avoid subreports (or at least those that potentially can 'oversize' by combining into the main recordsource it may work. You can try the things Colin suggested - might get you over the line. Or go via word. There are other ways to sum values, using dsum for example.

The main report looks the same as this one- same sections, the six sub reports in the detail section each look like the attached image. The main differences 1) Each of the six sub reports has a report header which serves as a title for that group of records and 2) each of the six has a 'Total' line in the footer which sums the values 'EFGAmount' for all of the detail records.

I'm going to try to use the method you provided earlier to add the six sub reports by combining them with the main record source. I think it would start off by editing the record source like thins:

SELECT [2024_ORG_ST_Summary_ABCD].*, [Sub_Report_qrySource1].*, [Sub_Report_qrySource2].*, [Sub_Report_qrySource3].*, [Sub_Report_qrySource4].*, [Sub_Report_qrySource5].*, [Sub_Report_qrySource6].* FROM 2024_ORG_ST_Summary_ABCD INNER JOIN Sub_Report_qrySource1 ON ([2024_ORG_ST_Summary_ABCD].[State Name] = [Sub_Report_qrySource1].[State Name]) AND ([2024_ORG_ST_Summary_ABCD].[Tst Num] = [Sub_Report_qrySource1].[Tst Num]);

But I could use some help to finish this SQL construction, what is the syntax to incorporate the remaining 5 record sources?