Parameter error for field I didn't ask for?

[connie]

I'm confused! I have a report tied to a query. When I open the report it's suddenly giving me "Enter parameter value tblEquipment.Room_No". But I don't have ask for those fields anywhere on the report or the query.
I checked all the controls, the group & sort, everything, and those 2 fields are not on there. What can I do?

 

[Banana]

If you remove the fields named 'tblEquipment.Room_No', does it goes away?

 

[connie]

If you remove the fields named 'tblEquipment.Room_No', does it goes away?

Well I can't delete the 2 fields I'm getting errors for from that actual table because they're in use on other reports and queries, but not on this report or it's source query.

But on this report and it's query I don't have those fields at all.

 

[Banana]

When I said to remove it, I wasn't referring to the tables but rather the query. You say they're not in it. Okay.

Now, if you go to the query that's used to drive the report, and open it, do you still get that error? If so, then it has to be in the SQL somewhere. If you can open the query with no report and only get that prompt in the report, then the report may have had a hidden field that got left behind.

 

[connie]

When I said to remove it, I wasn't referring to the tables but rather the query. You say they're not in it. Okay.

Now, if you go to the query that's used to drive the report, and open it, do you still get that error? If so, then it has to be in the SQL somewhere. If you can open the query with no report and only get that prompt in the report, then the report may have had a hidden field that got left behind.

Oops, sorry, I misunderstood

When I open the query that drives that report, I do not get any error.

 

[Banana]

No problem; I could had worded it more clearly so my bad, too.

Good, that tells you the problem exists in the report. There may be a field that still references a control that wasn't included in the query.

Alternatively, if the report has a subreport, you need to check the query for that subreport as well.

 

[connie]

No problem; I could had worded it more clearly so my bad, too.

Good, that tells you the problem exists in the report. There may be a field that still references a control that wasn't included in the query.

Alternatively, if the report has a subreport, you need to check the query for that subreport as well.

Yes, it's good to know the problem is in the report, but I swear I've looked at it 50 times and I cannot find any control referencing it. I don't know what else to do! No subreport. I did a select all looking for hidden fields, I can't find any. This same problem is actually occuring on 4 different reports now and there are no errors when the underlying queries are opened. Same parameter fields in the errors. Is it hopeless? Will I have to recreate these reports from scratch?

 

[Banana]

Let's try and be thorough.

What if you create a new blank report, attach the same query to it, then open it?

If no errors, then select all controls from one of troublesome report and paste it in the report. The code behind the report won't copy over but let's not worry about it just yet. Does it still open without any errors?

 

[connie]

Let's try and be thorough.

What if you create a new blank report, attach the same query to it, then open it?

If no errors, then select all controls from one of troublesome report and paste it in the report. The code behind the report won't copy over but let's not worry about it just yet. Does it still open without any errors?

Interesting...I don't get errors for either of these steps.

 

[Banana]

Now if you have any code behind the report, copy it over. Can you still open it without any errors.

If so, then I suppose there was a field in there somewhere or maybe it was deleted but wasn't properly cleaned up for whatever reason.

 

[connie]

Ok, there was no code behind the report, so I saved this as the new report and closed it. It was fine...and then I reopened it and it is now giving the same errors as the others!

 

[Banana]

Hmm.

Let's make sure we understood the steps we did.

1) made a new blank report.
2) bound the report to the same query.
3) opened the report (without any controls) and there was no errors.
4) copied 'n' pasted controls from old report into the new report.
5) opened the report again and still got no error.
6) saved the report, closed it and opened it and error occurs?

Is that correct or did you take a slightly different steps?

 

[connie]

1) made a new blank report. Yes
2) bound the report to the same query. Yes
3) opened the report (without any controls) and there was no errors. Yes
4) copied 'n' pasted controls from old report into the new report. Yes
5) opened the report again and still got no error. Yes
6) saved the report, closed it and opened it and error occurs? Yes

Yes, those are all the exact steps I took and in that order. I have found out the problem though, completely by mistake. In the Property Sheet of the Form itself the "Order By" was set to those fields that the parameter prompts were asking for. I never set that and I have no idea how that ended up there? I've never even used that option, always just went to Group & Sort and added my sorts there, and in that area it does show the correct sorts and not these wrong fields. Maybe it was set up through an old Report Wizard setup and I copied that report in order not to have to redo the header and footer, etc. That makes no sense to me though that Access would have two separate areas for sort order like that and that don't update one another.

The thing I don't get also is how it ended up popping back into the brand new report after we did the steps above. Strange.

Well it's ERROR FREE now and thank you soooooo much Banana for being so kind & helpful!

 

[Banana]

Well, *headdesk*, who would thunk. I never ever use OrderBy property (using the query to do it anyway) so would not have had known.

However, to answer your question- If you have any means of choosing order by, perhaps by the toolbar/menu or in the datasheet view** select the column, it will fill in order by property and Access tends to save that property for the next use so that's what trapped you; when you used one of report and cleaned out the fields, that OrderBy is still there and thus you got that little box asking you for nonexistent value to order the records on.

The OrderBy, IMHO, is more likely to be a bad thing than helpful and for that reason I usually enforce my ordering in the queries and never in form/reports.

 

[connie]

Headdesk...lol. Well the only time I recall ever sorting by those fields in the first place was in the table itself in datasheet view...that would carry over into a report? Geez. I agree, I never use the Order By in the property sheet either and I think (may be biased because I just spend half a day in frustration) it's more bad than good. But thank you for helping me and hopefully some poor lost soul out there Googles something one day and finds this thread helpful!

 

[Banana]

It shouldn't carry from one object to other. I'd consider it bug if it did. Without any testing, though, I can only venture to guess how it could gotten sorted when it shouldn't have been sorted.

I created a quick report and looked at how I could evoke Order By but didn't see anything that would even enable me to sort the things. I also looked at the query builder which definitely allows sorting/filtering in datasheet view but it didn't even stick or carry over.

Would love to know how it happened about.

 

[Rich]

Order by in a query has no bearing on a Report, it has to be done in the Reports sorting/grouping options

 

[Banana]

Rich,

Thanks for the suggestion, but reading the OP, it sounds like the OrderBy got in the report's properties somehow even though OP doesn't use that particular OrderBy. I created a sorting in a sample report using Sorting and Grouping and it didn't carry over to the report's OrderBy.

You're right that ordering in a query has nothing to do with report, though. Would like to know how to reproduce this, though.

 

[Rich]

Unless it's a new feature I wasn't aware of a sorting option on a reports property sheet I can guess that either the op is getting confused between forms/reports or it's something that gets carried over when making a report from a form from the button on the form toolbar

 

[connie]

Yes, there is a sort option in the Report's property sheet called "Order by" and I just checked - it is in Access 2003 and 2007, I don't know about prior to that.

I followed the steps Banana had outlined exactly so I am stumped as to how this could have carried over onto the new blank reports just by copy/pasting the controls, but it did.