Report locked into/stuck in process of 'Formatting page, press ctrl+Br 2

[PreacherUK]

Sorry about the long title chaps. Not to mention that it doesn't even all fit

"Report locked into/stuck in process of 'Formatting page, press ctrl+Break to stop"

So I have a report that contains a sub-report. The main report shows loans and the sub-report shows the amount of/level of collateralisation we have for the loans.

On the report we do a small calculation to show a certain percentage of the collateral beside each loan.

This is only appearing to be a problem when the number of records in the sub-report increases beyond 5-6.

In fact I have tried incrementing the number of records steadily and it didnt seem to have a problem until I reached about15-20 records then I started to receive the 'Formatting page' message and the report just sits there and, well continues to format I suppose it doesn't however seem to reach an end.


So, from collective experience what causes this kind of problem for reports? What causes them to get stuck into this 'loop' of 'Formatting page'?

Do reports, in someway, reformat/reshuffle themselves when you flick between design and view mode, increment the number of records in the source table and then keep flicking back into view mode?


Cheers for any help

 

[Mabjro]

Are your relationships clearly defined? For example, if your recordsource is a query, and the query has fields from more than one table, if you do properly define the relationships between the tables you will get an exponential number of records returned. I have done this by mistake in the past. I get the exact same message you described. Also be sure that in the report you are linking child and master fields correctly or all of the records on the sub report will be returned for every record on the master report. This also can return an exponential number of records, causing an endless formatting process. Hope this helps.

Jonathan

 

[jiqjaq]

If you try to put too much data into a subreport - or any other control - sooner or later you will fill up a page on the report. When the data no longer fits, Access will attempt to reformat so that it can break the data into a piece that fits on the current page and the excess (overflow).

Each time this happens, Access will execute the _Format code of the offending section. (That's why there's a automatic FormatCount variable to track the number of times the section's formatting had to be done.) This will happen again & again until Access has determined that all data from all controls in that section have been assigned to a specific page.

Even if there is no code in the _Format, that section will still be rerun. You won't move to the next data record, but the controls will fill up with the data just like they did the first time through.

If you aren't careful and have concatentation or other code that expands fields each time it executes, you could get into an endless loop formatting. Most of the time it doesn't make a difference because the data simply overflows to the next page and that's the worst of it.

Oh, but those times when it does matter! Such is the case for subreports. Not only does the data need to fit on the subreport, it also must fit onto the main report. If it can't fit, since it's part of the offending section, it will be executed again & again, etc.

In the VBA code you can prevent most of the problem by putting the certain blocks of code in an IF, and only executing when FormatCount =1. This does the trick for data generated via code. When the data is generated via bound control expressions, you need to redesign.

If you go into Debug mode and track the FormatCount, you'll probably realize what is wrong.

 

[PreacherUK]

Thanks for the feed back guys.

Jonathan: The data is all coming from a single table. The main report is feed by one query and the sub a different query on the same table.

I think though that jiqjaq has hit the nail on the head. Though not produced via code I had suspected something as simple as 'having too many records to display' in the sub report, particularly as it had no problem with 2-5 records but fell over when I asked it to show 15+.

I tried running the report on a much higher specced machine and there was no problem, however the machines here at work are going to force me to do some redesign.

Thanks again for the response.

 

[LindaClarke]

Have you solved the formatting problem yet? (You've had 3 days!)

I've encountered the formatting infinite loop on reports where I've used a number of group headers on which I've set the repeat section option (so that a value repeats at the top of a new page). Even though this is a built in formatting option Access is not always comfortable processing it! The number of records does not appear to be the issue as Access will happily process 20 pages of data but will the fall over when the selection criteria selects just 3 records.

Resetting the report's Repeat Section to No (the default) resolves the problem.

Hope this helps