Changing records without reopening

[jonathanchye]

I have a report which unfortunately has a lot of controls. It takes around 15 seconds to open in Print Preview.


What I'm trying to do is to open the report and set the visible property to false when the DB loads. When the user wants to print an order the report then finds the record and is visible again. On the report's "Unload" event I've added the code Cancel = true and Me.visible = false which keeps the report hidden even though the user clicks "Close" after print preview.


So far so good however I noticed the report will not change records if the user selects another order. I guess this is because I've initially loaded the report with a filter (docmd.openreport "Test",,,"ID = 1" for ex.)


I've tried reopening the report again but that won't work. The only way that seems to work is to call a sub in the report using Me.filter = "ID = xxx" Me.filteron = true but then it will take the same time "loading" again even though the report was already loaded but hidden.


Is there any solution for this? Bookmark, go to record etc doesn't seem to work for reports.


If I open the report first time without any filters the DB crashes as there's thousands of records...

 

[jonathanchye]

Wrote function to count controls in the report and there's 335 controls...


I've done all I can to optimise the recordsource, no grouping/sorting but can't really remove any controls due to design.


Any other tips to speed up loading this report?

 

[JHB]

..
If I open the report first time without any filters the DB crashes as there's thousands of records...

It shouldn't crash because of that, so I guess something is wrong/not valid.

 

[jonathanchye]

It shouldn't crash because of that, so I guess something is wrong/not valid.

I've tried using the code below to open:


Docmd.openreport "REPORT", acViewPreview ,, , acHidden
Reports!REPORT.Report.visible = false


it opens but takes forever to render through all the records so the DB hangs.


The detail section of my report has the bulk of the controls (around 300). The recordsource query opens almost instantly with all the records.

 

[jonathanchye]

Hmm, finally some break through. I noticed if I removed the recordsource the report loads much faster!


The record source is a query and if I open the query independently it loads quick too.


Mystery deepens...