The Microsoft Access database engine does not recognize ‘rep……’ as a valid field name or expression (1 Viewer)

[SachAccess]

I appreciate it's difficult for you, but a snippet of code really should possible to post without breaking confidentiality requirements. It's next to impossible to diagnose the problem, when we can't see the code, and we aren't really sure what you are actually clicking. Maybe you need to pay for a consultant to advise you - maybe share a Teams session with you, and view the issue first hand.

Thanks for the help. I agree with you. Have a nice day ahead.

 

[SachAccess]

OK, two things pop out at me:

First, that is a specific message from the ACE engine. It is a statement that the engine attempted something and failed to find whatever the name really was. That means that SQL is involved. It is not VBA trying to find something. It is SQL trying to find something. There are a few ways that can happen. I'll get to that in a moment. Further, if there really IS a dot in that name, it would seem to me that the first part of the name is the query name and the second part is the field name. As a sanity check, if that dot really is there, does the name to the left match the name of the report? If so, then the name to the right is all you need to find. If not, you might have found the problem right away.

Second, this happens when you try to launch a report. A single- or double-click on a report item doesn't make a difference if there is no code for the double-click. Access just converts that to a single click and carries on with whatever it needs to do next, so the absence of double-click code is no biggie. The execution of single-click code is also no surprise.

Even if the report was originally built based on a stored query, there might be some differences between the original query and what the report sends to ACE. Reports analyze the query so that they can impose their own ideas on the data, particularly if the report has grouping and sorting built-in. If the report's designer manually imposed sort or group options, that would be enough for Access to create an alternative query as the source of the data set. These would qualify as "hidden" queries so you might have to show hidden queries when trying to find the culprit. The name of the "alternative" query would have a tilde (~) as its first character and would resemble the name of the report.

The ways that SQL gets involved here would be either the actual SQL of the report's .Recordsource or as a quoted string in one of the arguments of a domain aggregate like DLookup" or "DSum" or "DCount" in a page header, report header, group header, or detail section on the first page. The criterion for a domain aggregate doesn't get checked until you actually open the aggregate function. Or you could have opened a separate recordset based on a quoted SQL string for some reason behind the scenes of the report. You should be able to search the report's VBA code for the string in question. Since it is related to the report, that really narrows where to look. If you can't find it in the VBA code, it isn't likely to be in any other function.

If the code search draws no result, then look at the form in design view and look at its .RecordSource - and if there is a sub-report as part of the design, it would be possible for there to be an error in the field name of the parent/child linking fields. The biggest chance is the main report's .Recordsouce. I would say that there is a chance that an automatically generated report would make the reference, except that if it was automated, the automation wouldn't select an invalid field. So this is PROBABLY the result of someone "adjusting" some element of the report's design or query.

Thanks a lot for the help. Please give me some time, I will try to provide as much details as possible. If possible will post code too.
Have a nice day ahead.

 

[Pat Hartman]

The only possible reason that field names could be sensitive is if they contain actual data that someone would recognize and that leads us to much worse problems.

I get that you don't want to post the app. But don't confuse us and yourself by hiding the code that is causing the problem. Access is telling you that it can't find something. That means that your name is wrong and we aren't going to be able to help you to figure that out because the actual name is a secret and we can't see the database anyway. Does the form with the mystery name exist? Can you see it in the list of forms? Does the mystery field name exist? Can you see it in the RecordSource of the report?

And finally, are you aware that MS rewrites your RecordSource query when you first run the report? The error that causes is that if you have used a column name in VBA or a macro but have NOT bound that column to a control on the report, you will get an error.

Try creating a control on the report bound to the "dummy" field. You can make it tiny and hide it so it doesn't interfere with the layout. When I put hidden controls on the layout, I make the background bright yellow as a reminder of why they are there and that they are never visible.

 

[JonXL]

Guess: the query underlying the report references that field OR a control on the report references the field.

And the field doesn't exist.

ABE: And there is no click or double-click event. OP just means opening the report from the nav pane.

 

[SachAccess]

Apologies for late reply. Am away due to some emergency.