Access is great on SharePoint.
It sites like a magic interface between list data and your front-end of choice. In my case just using Access as front-end in SharePoint .
While fiddling with Access in SharePoint in order to create a custom CRM backend I kept getting the following error:
This is the error I get on a client.. not very specific.
So going on the local machine I get:
Invalid reference ‘Field_Name’ in expression. You may have attempted to use an undeclared parameter or a field that is not bound to a control on the form or report. This is in a report that renders on a form and seeing as it is my first little access number I had no idea how to get this working.
After some extensive troubleshooting I found the following link:
What it comes down to is that in the report you link the child to the parent using the parent’s Key ID.
In this case a main “ID” which then relates in the sub table to something like “ContactID” , this is all great and it is sitting in the table I am trying to query, but I am not getting anything done.
I checked the result set that gets populated in the report and found that the field in question “ContactID” is not showing in the report, so after dragging it in and setting visibility to false it worked like a charm.
Now I just need to hide the SSRS elements and add nice column headings. Sorted.