Reports locking up

[SimeonD]

Hi
Using an ADP in Office 2003. I have a report which has 20 sub reports.
Normally, it works fine.
However, sometimes, the report won't print. There is not error message. The
status bar just show "Formatting report. Press Ctrl+Break to stop." The
progress bar fills all the way up, then nothing happens.
Each sub report relates to a Client Section, which can be turned on or off.
So what I'm doing is turning off each section until I find out which sub
report is the problem.

I don't understand why I don't get an error message. I have code in the top
level report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_Detail_Format

If Not SectionFormatComplete Then
FormatSection
SectionFormatComplete = True
End If

Exit_Detail_Format:
Exit Sub

Err_Detail_Format:
msgError ("Presentation - Format Detail")
Resume Exit_Detail_Format

End Sub


FormatSection is a procedure which makes the sub reports visible if the
corresponding Section is turned on.
I would have thought if there was an error in the sub report, it would jump
back to the error handling in the top level report. Unless, of course, there
was error handling in the sub report.
Eventually, I would find the error, usually some field isn't filled or
somesuch.Very time consuming however.
If I step through the code, I will find that the code just keeps looping
through the detail format of each sub report. If I open the sub report on
its own, I will get the error message then.


So I'm looking for two things:

1) A way of knowing which sub report is the issue. And get Access to give me
the error message.

2) Rather than Access locking up, I'd like to generate some kind of time out
message. Anyone got an idea of how I would do that?

Thanks

Simeon

 

[Mary Chipman [MSFT]]

I'll leave the reporting/error messages aside to deal with the other
elephant in the room -- why your queries might be causing Access to
time out. Each of those 20 sub reports is accessing SQL Server data,
and the only way you'll be able to troubleshoot the data aspects of
the problem is to create a SQL Profiler trace and examine the SQL
statementsbeing passed to the server. Here's a couple of links to get
you started:

SQLS 2008 BOL: http://msdn.microsoft.com/en-us/library/ms181091.aspx
P&P How To: Use SQL Profiler
http://msdn.microsoft.com/en-us/library/ms979207.aspx

Once you have the Profiler trace in hand, you can correlate it with
what your VBA code is doing by putting break points in the individual
sub reports. HTH,

--Mary

 

[SimeonD]

Hi
I agree with the use of the profiler. However, when this issue happens, its
an end user who has the issue.
So I'd like to know what Section is causing the problem, so I have a better
chance of replicating the issue.
A few times it has been different versions of office have caused the issue,
it might work fine on Access 2003 but not on Access XP.
So the more pointers I can get, the better.
Thanks

 

[Mary Chipman [MSFT]]

What I'd recommend is that you try to repro the case by narrowing down
exactly which sub report/combination of factors is causing the
problem. So it might be some combination of break points, debug.print
statements, Profiler traces, or sitting over someone's shoulder when
it happens. There are too many moving parts plus
environmental/software/versioning aspects to your scenario which make
it impossible to diagnose/debug from afar without more detailed
information. As far as not getting decent error messages in your sub
reports, I'd post a separate question in the Access reporting
newgroup.

--Mary

 

[adubois-mt@hotmailcom]

Hi
I agree with the use of the profiler. However, when this issue happens,
its an end user who has the issue.
So I'd like to know what Section is causing the problem, so I have a
better chance of replicating the issue.
A few times it has been different versions of office have caused the
issue, it might work fine on Access 2003 but not on Access XP.
So the more pointers I can get, the better.
Thanks

 

[SimeonD]

Thanks Mary. I had cross posted to the reporting newsgroup in the hope that
someone might pick it up, but no luck
Might try some other newsgroups in the hope of getting lucky.

 

[Mary Chipman [MSFT]]

I think your scenario is too complex for anyone to be able to give you
a definitive answer right off the bat, which is why you haven't gotten
a response. As I said earlier, you're going to have to do some
troubleshooting yourself to narrow down the probable causes, and then
perhaps someone might be able to offer some useful advice. The experts
tend to not respond when they don't know, or sometimes if they see a
long thread, they figure someone else answered the question. So do a
little testing, and then start a new thread when you have a narrower
case, and you'll probably get some good advice. Good luck!

--Mary