"On No Data" causes crashes

[Kari]

Every time I try to use the "On No Data" even to cancel opening a report it
crashes Access. That is, if I insert the following code in the On No Data
event:

MsgBox ("No data for report)
Cancel = true

When I try to open the report in a circumstance where I know there is no
data, Access itself crashes ("Access has encountered a problem and needs to
close").

At first I thought it might be a bug in Access 2007 bug, but I haven't seen
any posts about similar problems. I created a new report (based on the same
query) and put the cancel code in the On No Data event. It works just fine,
cancels the report if there is no data. From this I conclude that there is
some code in my actual report that is somehow clashing with the Cancel and
causing the crash.

Do anyone have any advice on how to fix this? I'm stumped.

Thank you for your help (and all your help in the past!)

Kari

 

[Allen Browne]

There could be several things going on here, e.g. a problem with the printer
driver, a problem with the VBA binary, a bad index, a corruption in the
database, a Name AutoCorrect issue, or a JET issue.

To address a possible corruption, work through these steps in order:
Recovery sequence - Standard steps to recover a database
at:
http://allenbrowne.com/recover.html

If that doesn't solve it and the report is based on a query, temporarily add
criteria directly to the query similar to what you are doing in your report,
such that it returns no records. Does the query crash Access? If so, it
could be a bug like this:
Why I stopped using Yes/No fields
at:
http://allenbrowne.com/NoYesNo.html

If you have tried all that (including recreating the report using
SaveAsText/LoadFromText) but it is still crashing, the other possibility is
a faulty printer driver. Access uses the metrics from the driver to
calculate the layout of the report, so it can crash if the driver is bad. To
address this possibility, try setting a completely different local
(non-network) printer as your default.

 

[Kari]

Allen,

Thank you for once again coming to the rescue quickly and concisely. I've
had this problem with several different reports, several different times, so
I didn't really think corruption could be the problem.

I do compact and repair regularly, but this required a full decompile.

Thanks for pointing me in the right direction.

Kari

 

[Kari]

Allen,

Unfortunately I spoke too soon; I was in a hurry and my report seemed to be
working (it gave my "No data" Message Box, and didn't open the report, all
without crashing). However, when I went back to the database and tried to do
something else (open a form),THEN it crashesd (Access has encountered an
error and needs to close).

I've since tried the Save as tesx/Load from text and checked the query (set
the criteria so that there is no data, opened it in datasheet view, no
problems). Still no joy in Mudville.

The only other things I can think of to try, are to pull the report apart
one piece at a time, or to recreate it from scratch (which I don't think will
help--as I mentioned, this has happened with more than one report).

Any other ideas would be most welcome.

Kari

P.S. Although the yes/no field isn't an issue with this report, I do have
some yes/no fields in other parts of the database. Thanks for the info on
those--I think I will go back and fix those while I'm waiting for new ideas.

 

[Allen Browne]

What version of Access is this?
And what service pack?

If it's 2007, you may need to add a service pack:
http://office.microsoft.com/search/redir.aspx?AssetID=DC102486291033&Origin=HH102511011033&CTT=5

 

[Kari]

Allen,

I'm sorry I didn't include that information earlier. I am using Access 2007
with Service Pak 1 on Windows XP.

Kari

 

[Allen Browne]

Not sure what to suggest, Kari.

Virtually all my reports call a generic function that cancels the No Data
event, and they work correctly on A2007 SP1 on XP SP2 (and on Vista.) So
there must be another factor here.

The fact that the decompile solved the problem initially hints that it could
still be a VBA corruption issue. You might like to try this:

1. Copy the code out from the report's module, and save as a text file. Do
this for any subreports as well.

2. Set the report's HasModule property to No, answering Yes to the
confirmation that you will lost the code. Repeat for any subreports.

3. Compact the database.

4. Use the undocumented SaveAsText to save the report as a text file, e.g.:
SaveAsText acReport, "Report1", "C:\MyFolder\Report1.txt"
Repeat for any subreports.

5. Verify that the text file(s) exist. Then compact the database again.

6. Ask Access to create the report(s) again from the text file(s):
LoadFromText acReport, "Report1", "C:\MyFolder\Report1.txt"

7. Open the report's module, and paste in the code you saved at step 1.

8. Compile.

 

[Kari]

Allen,

Thank you for sticking with me on this very annoying problem.
Unfortunately, since this is not an essential feature of the database I need
to move on for now. I was trying to put a little polish on the database, but
it will have to wait. I will try your next round of suggestions when I have
some extra time, and try to post back with the results. Thank you for all
your input.

Kari