format page takes a long time

I have a report that has two subreports. when it opens formatting page can
take up to one hour. This is not acceptable. I have run the underlying
queries and they run very quickly. How can the formatting page be sped up?
Thank you.

Okay, so you are trying to trouble-shoot what's going on. Once you identify
what it's doing, you will know what do to about it. Suggestions:

1. If you are using A2007, make sure you have SP2 applied:
http://www.microsoft.com/downloads/d...displaylang=en

2. Page breaks are one of the likely culprits, so do without page breaks in
the right place until you get it sorted out. Open the main report in design
view, and set Force New Page to None for the Detail section, and for all
your group headers and group footers. Still in report design view, open the
Sorting and Grouping pane. For each group header/footer, choose:
Do not keep group together on one page.

3. Open each subreport in design view, and do the same thing there (i.e.
don't force new page for each section, and don't keep together for group
header/footers.)

4. Test. If that's solved the problem, you can now try different
combinations of settings, until you get the page breaks you want but without
taking forever to try to plot a solution. If it has not solved the problem,
continue to the following steps.

5. If you are still stuck, provide indexes on the fields involved in
criteria or sorting. These include:
- Link Master Fields and Link Child Fields properties of the subreport
controls.
- Fields in the Sorting and Grouping dialog.
- Fields in the Filter or OrderBy properties of the report.
- Fields in the criteria of the source queries. (Omit any sorting from the
source queries.)

6. Watch out for any fields where the data type is not clear. There's no
problem with fields that come from tables, as the data type is defined; the
problem is usually with calculated expressions, where you may need to
explicitly typecast.

7. The next step is to remove any expressions from the report to see if they
are taking the time. For example, if your report has a dozen expressions
that use DLookup(), that could be a factor.

Chances are that if it's not an SP issue, it's to do with the way Access is
plotting the page layout and then retreating and doing this over and over
again. If so, the page break issues (steps 2 and 3) should sort it out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"seeker" wrote in message
...
I have a report that has two subreports. when it opens formatting page
can
take up to one hour. This is not acceptable. I have run the underlying
queries and they run very quickly. How can the formatting page be sped
up?
Thank you.

Thank you allen. I have done all the suggestions and it is still taking
forever. When I send it to the printer it spools for similar amount of time.

"Allen Browne" wrote:

Okay, so you are trying to trouble-shoot what's going on. Once you identify
what it's doing, you will know what do to about it. Suggestions:

1. If you are using A2007, make sure you have SP2 applied:
http://www.microsoft.com/downloads/d...displaylang=en

2. Page breaks are one of the likely culprits, so do without page breaks in
the right place until you get it sorted out. Open the main report in design
view, and set Force New Page to None for the Detail section, and for all
your group headers and group footers. Still in report design view, open the
Sorting and Grouping pane. For each group header/footer, choose:
Do not keep group together on one page.

3. Open each subreport in design view, and do the same thing there (i.e.
don't force new page for each section, and don't keep together for group
header/footers.)

4. Test. If that's solved the problem, you can now try different
combinations of settings, until you get the page breaks you want but without
taking forever to try to plot a solution. If it has not solved the problem,
continue to the following steps.

5. If you are still stuck, provide indexes on the fields involved in
criteria or sorting. These include:
- Link Master Fields and Link Child Fields properties of the subreport
controls.
- Fields in the Sorting and Grouping dialog.
- Fields in the Filter or OrderBy properties of the report.
- Fields in the criteria of the source queries. (Omit any sorting from the
source queries.)

6. Watch out for any fields where the data type is not clear. There's no
problem with fields that come from tables, as the data type is defined; the
problem is usually with calculated expressions, where you may need to
explicitly typecast.

7. The next step is to remove any expressions from the report to see if they
are taking the time. For example, if your report has a dozen expressions
that use DLookup(), that could be a factor.

Chances are that if it's not an SP issue, it's to do with the way Access is
plotting the page layout and then retreating and doing this over and over
again. If so, the page break issues (steps 2 and 3) should sort it out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"seeker" wrote in message
...
I have a report that has two subreports. when it opens formatting page
can
take up to one hour. This is not acceptable. I have run the underlying
queries and they run very quickly. How can the formatting page be sped
up?
Thank you.

Wow: if it's not the SP, and it's not the retreating, and it's not to do
with the printer driver, I don't know what to suggest.

You may want to consider adding some Debug.Print statements to the Format,
Print and Retreat event procedures of the report. Print the primary key
value, and see if it is in fact advancing and retreating.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"seeker" wrote in message
...
Thank you allen. I have done all the suggestions and it is still taking
forever. When I send it to the printer it spools for similar amount of
time.

"Allen Browne" wrote:

Okay, so you are trying to trouble-shoot what's going on. Once you
identify
what it's doing, you will know what do to about it. Suggestions:

1. If you are using A2007, make sure you have SP2 applied:
http://www.microsoft.com/downloads/d...displaylang=en

2. Page breaks are one of the likely culprits, so do without page breaks
in
the right place until you get it sorted out. Open the main report in
design
view, and set Force New Page to None for the Detail section, and for all
your group headers and group footers. Still in report design view, open
the
Sorting and Grouping pane. For each group header/footer, choose:
Do not keep group together on one page.

3. Open each subreport in design view, and do the same thing there (i.e.
don't force new page for each section, and don't keep together for group
header/footers.)

4. Test. If that's solved the problem, you can now try different
combinations of settings, until you get the page breaks you want but
without
taking forever to try to plot a solution. If it has not solved the
problem,
continue to the following steps.

5. If you are still stuck, provide indexes on the fields involved in
criteria or sorting. These include:
- Link Master Fields and Link Child Fields properties of the subreport
controls.
- Fields in the Sorting and Grouping dialog.
- Fields in the Filter or OrderBy properties of the report.
- Fields in the criteria of the source queries. (Omit any sorting from
the
source queries.)

6. Watch out for any fields where the data type is not clear. There's no
problem with fields that come from tables, as the data type is defined;
the
problem is usually with calculated expressions, where you may need to
explicitly typecast.

7. The next step is to remove any expressions from the report to see if
they
are taking the time. For example, if your report has a dozen expressions
that use DLookup(), that could be a factor.

Chances are that if it's not an SP issue, it's to do with the way Access
is
plotting the page layout and then retreating and doing this over and over
again. If so, the page break issues (steps 2 and 3) should sort it out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"seeker" wrote in message
...
I have a report that has two subreports. when it opens formatting page
can
take up to one hour. This is not acceptable. I have run the
underlying
queries and they run very quickly. How can the formatting page be sped
up?
Thank you.