Trouble with Access 2000 reports

July 10, 2007 5:39 PM

My Access 2000 report keeps looking for a field that no longer exists. HALP!!

I built a lengthy report in Access that was sorted & grouped on a location field (Chicago, Denver, etc.). I realized that some of the fields I wanted to include in the report were in a different table, so I made a query that included all of the relevant tables. Unfortunately, now there were two location fields, and Access didn't know which one I wanted to group by (even though it was clearly selected from Table1 in the dialog box). So I went into the tables and only kept one location field.

Now Access can't display the report because it asks me for Table1.LocationName ("enter paramater value" dialog box) but there is no such field in the query, the table, or the report. This is true even after removing all sorting/grouping. I really don't want to rebuild this thing if at all possible. because I've spent so much time formatting.

PLEASE HOPE ME!!!

posted by desjardins to computers & internet (4 comments total)

There's still a field that's trying to use Table1.LocationName as the data source or default value. That is, there is a field that is still trying to reference that field name. You're getting the dialog box because it doesn't exist.

Go into Design View on the report and check the properties of each textbox. It may take a while but it's got to be in there somewhere.

Or you can try to add and run the FindInFormReport() function on this page in your report. I've never used it but it looks solid.
posted by junesix at 6:49 PM on July 10, 2007

I'm not advanced enough to use the code. I gather that I'm supposed to cut & paste it somewhere, but I'm not sure where (I opened up the code window in the report and put it there, but I'm not sure what I'm supposed to do next. It doesn't seem to do anything on its own.)

I checked all the text box properties... it's not there, I swear.
posted by desjardins at 6:58 PM on July 10, 2007

recreate table1.location name. put some bad data in it--say all one word like 'accesssucks'. run your report. it'll hopefully identify the faulty textbox for you.

as a side note, if you're not advanced enough to use the code, then you are probably just better off rebuilding the entire report.
posted by lester at 7:31 PM on July 10, 2007

When you checked each textbox, did you click on each one you saw or did you select them through the Object dropdown menu? If you just clicked on the ones you could see, there's a chance the culprit textbox is sitting underneath another object.

I'd test the code for you but I don't have Access at home.

If it comes down to it and you need to rebuild the report, don't delete the current one. Start a new report in design view and copy over the objects from the existing report, a few at a time. When you copy-paste the objects, they should retain all the formatting. Swap back into report view after every copy-paste task and make sure you don't get the dialog window. When it finally does appear, you should be able to undo one previous copy-paste and then diagnose the problem object.
posted by junesix at 9:34 PM on July 10, 2007