07 prompts for table field

[NetworkTrade]

this is definitely an Access07 question;

design a Report sourced on a query - and upon open it prompts for the need
for a value naming a field that is not in the query and not in the
report......it is in the table that the query is designed on....

the symptom is exactly like normal Access where there is a field in the
report which can not find that matching field in the source; however in this
case the field is not and has never been in the Report......

in one case I can't add that field because is a query that is group/sum.....

anyone else seeing this....??

 

[Allen Browne]

Something in the report must be attempting to use a field with that name.

As well as checking in the Control Source of the text boxes etc on the
report (including expressions), look in the report's properties (Filter,
Order By), and in the Sorting And Grouping pane.

Presumably you have already tried running the source query directly to
ensure it is not looking for the field.

If you can't find where the field is referred to, this may help pin down
what's asking for it:
Where is a field used?
at:
http://allenbrowne.com/ser-73.html

 

[NetworkTrade]

Yes - thank you very much - there definitely is a bug but you showed me how
to identify and resolve. I found the irrelevant field in the Report's
OrderBy Property.

Given that the report was generated via Report Wizard and that field is not
in the record source I believe this process to be the root cause. It is a
repeatable thing in my case because this was a very simple report so I
deleted and remade that report a few times and always had this error. My
guess is that it has something to do with the fact that the field is a key
field in a table - but that is the extent of my insight.

Possibly related; I have found on a couple occasions, and have also helped
one person here on this site; where a report/subreport would not open - and
the record source was a table. Changing the record source to a query of that
table, with all fields, fixed the problem. I don't know if this is a bug or
a new philosophy/concept that reports must only be sourced on queries.

I don't know if these two issues are related but am seeing both phenomena in
07 Reports. Does one need to look for updates of Access07 downloads??
perhaps I have an older build....my machine auto updates is turned on
however....

thanks again,
NTC

 

[Allen Browne]

Service pack for Office 2007:
http://office.microsoft.com/search/redir.aspx?AssetID=DC102486291033&Origin=HH102511011033&CTT=5

More info on adapting to Access 2007:
http://allenbrowne.com/Access2007.html

I haven't seen the specific symptoms you pose, but it could be a combination
of a bad index and a Name AutoCorrect error.

Here's a standard recovery sequence:

1. Uncheck the boxes under:
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:
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.

7. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

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,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

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

 

[NetworkTrade]

appreciate this wealth of info....will save for reference