Access Reports crash on NoData event

[Guest]

I have been working on an application which I think was originally developed
in Access 97. It was converted to Access 2007 format, but the reports are
crashing Access at times, especially when reaching the Cancel = True on the
NoData event if non-existent criteria are entered to run the report.
I came up with a solution for the NoData problem. I created new blank
reports and copied all the grouping and sorting, controls, and code, using
code utilities I created. Once the report is created in Access 2007 it does
not crash on NoData, but still crashes at other times.
What could be the cause of the NoData crash?

 

[Allen Browne]

Hmm. The event should not crash Access, so something else must be going on,
either with the queries (that return nothing), the compiled VBA (from the
old version), or Access' confusion about the objects (resulting from Name
AutoCorrect issues.)

Try this sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[jason-sct]

Actually it's the "On Close" report event that's crashing your Access 2007.

The "On No Data" event is fine. That event's code code "Cancel = True" or "DoCmd.CancelEvent" is fine.

My report, converted from Access 97 to Access 2007, now closes fine after removing: "DoCmd.Restore" from my report's "On Close" event.

Although, I wonder what was the original goal of having a Restore command when closing a report... seems to work fine without it.