Access Subreport Performance Degrading on Page Break

2
votes

It seems as though my reports take 3 times longer to render when a subreport of a subreport has to page break.

Example of where data breaks;

enter image description here

I have 2 versions of this report, one where one of the Segment Labor Subreport's controls contains an IIF statement to evaluate whether a Labor Comment field is empty. If it isn't empty, I insert Chr's for return & newline, then the Labor Comment itself. The version of the Parent Report that doesn't contain this extra Labor Comment makes the sub-sub-report small enough to fit on one page.

If the Segment Notes is long enough to break onto the 2nd page, there's no issue, the report is still only 2-4 seconds from being requested to delivering a PDF. When the Segment Labor subreport has to break onto a 2nd page, it's a MINIMUM of 20 seconds.

And advice on how I can either programmatically anticipate this issue, or side-step it altogether?

1
ms-accessvba

1 Answers

1
votes

Found the cause! The problem was being caused by two separate aspects of the report, working together.

Contrary to my extremely simplistic Balsamiq mockup in my question, the Labor subreport contained the following columns;

  1. Technician
  2. Start & stop times
  3. Hours
  4. Labor code
  5. Value from a lookup table when certain labor codes were used.

Two aspects of the subreport that I had isolated as possible causes;

  1. There was a DLookup being performed on each record in order to calculate column 5. This worked OK when the lookup was on the same line, that is,

    =[LABORCODE] & " (" & DLookup(DisplayValue,LookupTable,[LABORCODE]) & ")".

  2. Using an IIF, a value being returned from the lookup on each line of the subreport would be prepended with Chr(13) & Chr(10) before the value itself.

(really poor) Explanation of the Solution

I think what was happening had to do with the DoCmd.OutputTo process trying to figure out where the page-break was going to fall many many times before deciding on what to do. By removing the DLookup and adding the info I needed as a JOIN in my source query, I not only grabbed the value out of the lookup table much faster, but added the conditional CR/LF to each line while rendering and printing the report to PDF in 3 seconds. Successkid.jpg.

Moral of the story is you might look at a problem and say, "I should use DLookup here". Now you have #NAME? problems.