I have a report which runs upwards of 5-8+ minutes from start to finish. The record source is a query which, when run standalone, takes 5-10 seconds to complete. I do have some code in the detail section which is executed during the OnFormat and OnPrint. The record source data is presented as a crosstab of up to 48 months of monthly data. Since I never know what months are going to be present in the data the report uses array type data referencing to pick up the data from the record source and place it in the proper column on the report in the detail OnFormat event. To determine where I was having a problem I logged every time I enter the OnFormat and OnPrint. According to the log timestamp it is taking 7-9 seconds to process the code in the Detail1_Print. I realize some of that time is used to run the App_AddLog function, but without any logging the report still runs longer than I would have expected.
The detail section’s OnPrint appears to be where the problem is, but the code is very simple. I am looking for the minimum value in “column A” and the maximum value in “column B”. At the end of the report in the ReportFooter I print those values. I have even moved the code from the OnPrint event to the OnFormat event without any improvement. Here is the OnPrint code:
Private Sub Detail1_Print (Cancel As Integer, PrintCount As Integer)
___ Dim wRetVal As Variant
___ wRetVal = App_AddLog("FCM", "rptProcessKDates", "intColumnCount=" & intColumnCount & " ; conTotalColumns=" & conTotalColumns, " ", "Detail1_Format()")
___ If Me.PrintCount = 1 Then
_______ If Me("Col1") <> -1 And Me("Col6") < dteMinEffDate Then
___________ dteMinEffDate = Me("Col6")
_______ End If
_______ If Me("Col1") <> -1 And Me("Col7") > dteMaxExpDate Then
___________ dteMaxExpDate = Me("Col7")
_______ End If
___ End If
___ wRetVal = App_AddLog("FCM", "rptProcessKDates", "Error: " & Err & "-" & Error, " ", "Detail1_Format()")
The report appears to go through all the format events for the report for every record source row. It then starts over again going through the format events and print events. I assume the first pass is to get an overall look at the report and calculate the number of pages. This is relatively fast. It is the second pass, where it executes the OnPrint event, which takes time.
Is there anything else happening behind the scenes during the OnPrint that might explain why it is taking so long? What happens between the OnPrint event and the next OnFormat event? Could printer drivers or connectivity to printers be an issue? The report does print correctly.
I don't have an answer to your problem but can you please share all of your code for this report? I too have a crosstab that returns anywhere from 2 – 60 months of data and can’t figure out how display this in a report since. Any help would be greatly appreciated!