Speeding up the formatting of a report

[xsnrg]

I have a report with the following scenarios...
- a subreport in the page header
- SQL is a TRANSFORM statement (CrossTab query) to create the data
- 14 objects in the detail section
- only the following code in the Detail.Format section
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Color" Then
If ctl.Value = 100 Then
ctl.BackColor = 14803425
Else
ctl.BackColor = 16777215
End If
End If
Next

But it takes 1 full minute to format each page of this report. Takes forever to print.

Any ideas how to make it run faster?

 

[dhookom]

Remove any reference to [Page] or [Pages]. If this doesn't work and the query seems to scroll to the last record quite fast then I would recommend creating a temporary table of report data. Append your records to the table and then open the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xsnrg]

I've tried all of this. I actually have a couple other similar reports that I did run a Delete and Append SQL statement first and run the report on a static table. Worked great. This one is not helped by this. It runs the query fast but still hangs on the formatting step, on each page.

 

[dhookom]

What's in the subreport? Is it static? Is its Can Grow set to No?

How/where are you using the Crosstab? Do you have static column headings? Does your crosstab have values entered into the column headings property?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xsnrg]

I have a system reporting on a project management system.
A form allows the user to select upto 11 tasks and any number of projects. I then report on the % Complete of each project for each selected task.

The form generate two static tables:
- tmpWorkspaces (the selected Project Names)
- tmpTaskRank (selected tasks and the order by A, B, C, etc)

* The subreport is the "Label" for the tasks in order by the "rank" letters.

I have attempted with with the Transform statement as the record source as well as creating a static table and setting the report based on the static table. Either way only changes the first page by a few seconds difference. Either way still requires a minimum of 1 minutes per page to print or preview.

It's got me stumped...

 

[dhookom]

The only further suggestion I have (before leaving on a 10 day business trip) is that if you are reporting the results of a crosstab, take a look at the crosstab report samples I created at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xsnrg]

I acutally used your example for my first CrossTab Report about 2 years ago. However, I changed it around a little on this one for more flexibility. I'll place a zip file example on my FTP site later tonight and forward to you.

You can let me know what you think of my version.

Just so you know, I have 8 reports all using my modified cross tab scenario. The other 7 all work out fine and there are very little differences. So I'm not sure what it is about this one that is hanging...