Subreport blanking out

[jessgold]

I really need some help on a report I am making for a client. Any time a sub report grows beyond a certain size the records just suddenly stop. There is a bunch of white space I can scroll though as if the records were accounted for space wise they just are not displaying. The sub report will be fine at any number of records on its own but when placed in a report footer as a sub report anytime it gets beyond 1800 records or so it just blanks out past that. If I make the detail section on the sub report taller(say 10 times the height of one record) it will blank out at record 180 or so. it appears to be a bug of some type I cannot solve. after all the blank white space that is equal to whatever amount it should be showing if the records were visible the next sub report will display. it too is subjected to the same limit on height before blanking out. I have tried everything I know of. I have changed the box properties of the sub report, the footer property of the main report, the detail section of the sub report, the report properties for both reports and nothing will affect this. can grow , can shrink, auto size nothing affects this. I am beginning to wonder why I cant find anyone else on the internet with the same problem. the problem has shown on two different pc's. both running windows 8 pro 64 and student office 64 2010. any help would be appreciated or someone with a 3000+ record database setting up two reports at 11x17 and seeing if your subreport blanks out too or will display more then 3000 without issue. I suspect the size of the report width and windows 8 might be related to the problem. Thank you all for the assistance I truly appreciate it.

[jessgold]

Update I have resized a test report to a standard 8.5x11(including margin) and deleted everthing from the subreport but the record counter and one field and everything from the main(container) report no luck sadly and it did not seem to affect the cutoff point.

[jessgold]

ok I eliminated everything from the subreport and altered the query its based on to have only one field. I changed the detail section of the sub to have a grey background so I could see where it ends and the same thing. now all I have is a blank report holding only a blank subreport with a grey background in the detail section and the same thing happens. it will blankout in the same place. GRRRR this makes no sense. Its looking like some kind of access subreport hard limit but why have I not been able to find anything about this if that is the case? can anyone test this on their access so I know im not crazy(getting close though lol)

[jessgold]

ok I added a standard size record count box to the sub report and shrank the detail setion of the sub to match the box size. I get to 1311 records before blankout. viewed on its own the subreport shows 2000+ with no issue. in print preview the records will not blank out in the sub only in report view will the blanking happen. btw record limit return has been uped to 100,000 with no effect on the blanking issue.

[rpeare]

I'd like to ask a couple of simple questions since it's not clear in your posts.

Are you seeing blanks in the PRINT PREVIEW or when you actually try to print the report. I've had instance in the past where an item is not visible in print preview but comes through at print time without any issue.

Access does have some size limitations (I think a vertical 22' is one of them, or it used to be) but I've never heard of it being an issue for an over abundance of records in a subform. I just did an quick example for myself to make sure I wasn't talking out of my ass but I created a main/sub form where the subform had 10000+ records and they showed properly in print preview but I didn't try to print it. Any chance you could post a sample database without any private info. I'm enclosing my example (it's access 2000 which is all I have available right now).

Test.zip

[jessgold]

Thank you for the reply rpeare. I have opened your example in "Report view" and I am witnessing the same issue of record cutoff in report view on your example. it is the only place I have had the issue on all reports as print preview works fine. When you view your report in "Report view" do you see the same issue as me?

[jessgold]

for me the report view cuts off(though the sub report border continues long past) at record 2048 in your shared example

[jessgold]

I would be relieved to discover this is a windows 8 incompatibility as the client does not use windows 8 so it would become a non issue and I could move on in designing but if it is inherent to access I need to know to change the view in all long reports.

[jessgold]

can Anyone try this test2.zip database to see if you see the same issue when viewing "Report1" in "Report View". Please post if the issue is there or not along with your os and version of access. After opening the report just scroll down until it blanks out past record 2048.

[rpeare]

Don't use report view :P use print preview!

[jessgold]

Thank you again for the great example and taking the time to look at this issue rpeare. Is this in fact a sub report limitation in all versions of access? Has anyone else tested this for third party confirmation? Is there no way around this limit for clients that wish to view multiple long reports in "Report View"? If I did not have to combine more than one report all would be well. Sadly, that is not possible. The client may wish to know why he can't view the report in "Scrollable" format. The only answer I have currently is "Its a limitation of access". If anyone can solve this issue or confirm this is a limitation of access please post here for others that may face a similar issue. Thank you once again rpeare and thank you to anyone else that can add their experience with this issue to this thread.

[June7]

I appear to be seeing records into the 17000+ numbers in report view (scroll is slow and jumpy) but all records in print preview.

Maybe don't need a report/subreport arrangement. Make a single report with a RecordSource that joins the two tables. Use report Grouping & Sorting functionality to organize. This works for me in report view.

An alternative is to eliminate need for tblMain in the report. How real is the sample data? Are there actually other fields from tblMain of interest in this report? If MainDesc is really that short, then could make it the PK and save as FK in tblSub. Eliminates issues associated with lookup aliases.

Why does data in tblSub replicate the MainDesc value in SubDesc field? If this reflects the actual data structure then there is already no need for tblMain in the report because the MainDesc can be extracted from SubDesc in a query.

So simplify the report and unless it is possible that some tblMain records have no related records in tblSub and you want report to show all MainDesc values regardless, ignore tblMain.

[rpeare]

I think june's suggestion is a good one. Create a single query instead of a report/subreport if you can, then you avoid this issue altogether because it seems like a spotty problem if you're using report view. Is there a reason you're married to using report view instead of print preview? the report view seems to be the issue so I'd avoid using it entirely if possible.

[jessgold]

Thank you for the suggestions. In the actual report it is quite complicated using dozens of fields from multiple tables all using joins. We have areas where different criteria is used in the summery sections. If I group on the subsections(and I do) I can get totals and averages for each section but I must use another ungrouped report to get totals of all the groupings together in a different format.

I.E. show cars grouped by color(red, green, blue, yellow) then give totals and averages for each color at the end of the report and in section group footers then all colors combined at the end of the report then totals and averages for type of cars (sports, family, suv) I could do this with iif in query to presort and display data by type but it seemed easier to just copy the report and query, alter the query then add the new sub report for each section. Once again thank you all for taking the time to help with this issue.

Btw I wonder if the issue is related x64 vs x32 or xp video driver model vs vista 7 8