Report with subreport running slow Results

I have read many posts on this subject but I can't seem to get anything to work. I have a main report based on a query and then a subreport based on a different query. the parent/child fields are txtReqUNID and ReqUNID. Sorted and criteria fields are indexed. I've removed the "order by" sql. I don't have any Dlookups or calculated fields. Both queries are based on the same table. I've run the Access performance analyzer. No matter how long the report has been loaded, each time you page down it is "running query" this takes a long time. I would appreciate any suggestions. The subreport definitely is the culprit but I don't know what to do about it.

I have a main report with a sub report. Individually they work fine, open fast and there are no delays on running the queries they are attached to either. Put them together and the report takes over 3 minutes to open. I've got a function on the subreport that works works out fields for calculation but it doesn't slow down the subreport on it's own.

Any ideas as to what I could do to speed things up?

Hi,

I have a master report with many sub-reports (x15 ish). It run's fine and I can navigate through the resulting report in Access report Viewer no problem. However, I added another subreport to the end which contained a chart. Now although it produces the report fine, when I go to navigate through the report (next page etc) using the Access Report viewer it displays "formatting page... (press ctrl-break to stop)" - or similar.. for every page I view. If I navigate to the Last Page I can go and make a cup of tea, visit my aunty, and dig the garden before it returns... goodness knows what its doing.

I found another post regarding the use of [Pages] in the footer which can cause an issue.. which I have removed with some improvement.. but I don't have that big a garden and my ear is getting gnawed off at my aunties.

Do you have any ideas why a chart would cause this sudden performance problem? Is there a way around it?

Cheers,
D.

Hi all,

New to here, new to access too. I only seriously develop stuff in Access since less than 2 weeks ago.

One of the report I have developed (its on live now) is PAINFULLY slow. So I want better solution with it. Its not super urgent but would be nice if I can improve it ASAP.

Requirement:
A customer requires a Access Report to print their invoice. BUT depends on the value in a piece of record, it can output 3 completely different layout. The invoices has to output in a specific order by order number etc. Both 3 type of report can use same query.

Currently:
I have created a MainReport and 3 x subReport (subrptFormat1, subrptFormat2 and subrptFormat3) inside it. Long story short the main report using a query (select * from tblInvoice order by invoiceID). The main form also has 2 x textboxes which will be popluated with value of "InvoiceID" and "Type"

3 x subreport will populate by query (select * from tblInvoice where InvoiceID = [Reports]![MainReport]![txtInvoiceID]).

At the same time, the main report will hide 2 of 3 subreports depends on the "Type" value of the record. In the VBA I do the following in the main report:

if [Type].value = 1 then
Me.subrptFormat1.Visible = true
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = false
elseif [Type].value = 2 then
Me.subrptFormat1.Visible = false
Me.subrptFormat2.Visible = true
Me.subrptFormat3.Visible = false
else
Me.subrptFormat1.Visible = false
Me.subrptFormat2.Visible = false
Me.subrptFormat3.Visible = true
endif

End Result:
So I have all these different format reports, consume the same query can able to keep the order I want, in ONE report window.

-----------------------------------------------------------

But as you can see its PAINFULLY slow. Generally speaking for EVERY record it needs to go thru the MainReport, then each of the subreport, then hide the other 2 subreports is not using at this particular page.

I thought there would be something I can "Programatically suppress" the 2 subreports I don't use for a particular page to run with condition on the "Type" value but I found nothing useful.

I also thought of creating just 1 x report with all the dynamic formating stuff in there (Control.Visible = true/false) but maintainence would be a nightmare PLUS adding another report format into it will become almost mission impossible.

Can anyone provide me a better solution to do what I am doing?

Thanks in advance


p.s. Some more background info:
The query can return 60,000 rows worth of records.

I have access 2003 installed on two different machines. One machine was built 6 months ago with the best hardware available. The other was an older slow IBM.

For some reason, I am experiencing what appears to be some serious time delay (talking in the range of seconds, sometimes tens of seconds) when building a report that has a subreport in it on the fast machine. Yet when I load the exact same database on the slow machine, the report opens instantly.

Any ideas? I'm lost.


Not finding an answer? Try a Google search.