Report Query Corruption when Access reopened

[Sam Davis]

Hi and thanks for reading,

I have a report that runs perfectly until Access is closed and then
reopened. When Access is reopened the Record Source of the report appears
fine but I suspect it's corrupted as Access asks for various parameters.

If I simply modify something trivial in the recordsource SQL (such as change
SELECT to sELECT) and save, it all works again until Access is closed and
reopened.
If I open the query in the query design window and then close it, it also
works again until Access is closed and reopened.
After each of the above the cursor thinks for a split second - I suspect the
SQL is being parsed and found to be correct.

I've tried:
- "Compact and Repair" - NO Luck
- Using a saved query rather than SQL as the recordsource - NO Luck

Any ideas????

Regards, Sam

 

[Allen Browne]

Suggestions:

1. Run through the steps suggested here:
Recovery sequence - Standard steps to recover a database
at:
http://allenbrowne.com/recover.html
This will address issues such as Name AutoCorrect.

2. If there are fields in the report's RecordSource that are not represented
by controls on the report, add a text box for them. The report optimizer
does not bother to fetch the data for all fields in the RecordSource, if it
cannot see where they are used on the report. Since they were not fetched,
when you try to refer to them in an expression, it pops up parameter
dialogs. You avoid the problem by adding a text box for those fields. (Set
Visible to No if you don't want to see the text box.)

3. In report design view, clear its Filter and OrderBy properties. Then make
sure there is nothing in the Sorting And Grouping box that is not a true
field also, as these can generate parameter requests.

 

[Sam Davis]

Thanks heaps Allen,

The decompile has fixed the issue. Hurray!

I'm developing using Access 2007, but in Access 2000 format. Prior to your
post I tested using Access 2000 and it all worked fine (could also create an
Access 2000 MDE). Clearly an issue with Access 2007, but it's fixed so
little point investigating further!

Sam

 

[Sam Davis]

Actually, my last post is not entirely correct.... thought I'd fix it up for
future Googlers...

In addition to the decompile... Allen's 2nd point below was also required...

In my case, the fields in the RecordSource that were causing problems were
all "LinkMasterFields" to subforms. Indeed one field is a LinkMasterField to
two subforms and it was asked for as a parameter twice. These fields were
NOT included on the actual report. To fix the problem it was necessary to
include each of these fields on the report in hidden (Visible=No) textboxes.

Bizzare situation, as it would be most unusual to ever display Primary or
Foriegn Keys on a report.

Thanks again Allen,

Sam

 

[Allen Browne]

Excellent! Glad you pinned it down, even though it was a combination
factors.

Thanks for posting details of the outcome for everyone's benefit.