I have an Access Web App and I am currently trying to make an OnClick macro for a field on one of my views so that when it is clicked, it will pop up another view to a specific record. Currently, my Where clause reads Where: [DistrictID]=[Districts].[DistrictID]
When I go into the App and click on the field with the macro, I get a pop up stating "Invalid field name 'Districts.DistrictID'."
The one and only site I could find that mentioned this error is: http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2014/08/04/access-app-invalid-field-name-lt-tablename-gt-lt-fieldname-gt-error-when-using-where-clause.aspx
This site says that it is due to case-sensitivity of the Where clause. My case matches my table/field exactly (I program in other languages, so I always match case just out of habit). I have quadruple-checked my spelling anyway, and even went so far as to copy-paste the field name into my Where clause. Still I get the error.
I have another view that does something similar with a different table/view that works perfectly (Where: [ContactID]=[Contacts].[ContactID]).
Does anyone have any idea why my Where clause is not working, or what I could be doing wrong?
Extra info if needed:
I would include photos, but the information in my database is sensitive, so I will do my best to describe the information in question:
I have 10 tables in the database. "Districts" is the one I am trying to work with. The Districts table has... quite a few fields, including DistrictID, DistrictName, EmailService, SpecialComment, and more. These four however are the fields being queried for the datasheet view which contains the macro. The OnClick macro is triggered for the SpecialComment field - when clicked, I want my District List view to pop up to the same district whose SpecialComment was clicked on (the Special Comment can run long sometimes, so if it is cut off by the size limit of the datasheet, I want people to be able to read the rest of the info without having to switch to another view and then find the district in the list). The District List view and the queried view both have the DistrictID field in the view, although it is hidden. I have tried unhiding the field in both views and it did not solve the problem - I had other Where clauses that used the DistrictID field before this that worked fine, so I doubted it would change anything anyway. Those Where clauses from before were substituted for other functions, so I don't have them to refer to to see why that one worked and this one does not.
If the view you are trying to open using OpenPopup macro action has a saved query as it's record source, then you must use the query name like this:
[FieldNameHere]=[NameOfQueryHere].[FieldNameInThatQuery]
If you are using a table name, then substitute the actual table name in the appropriate spot.
One thing to note which sometimes trips people up is that this technique won't work if the view you are trying to open uses an Embedded Query as its record source. The reason for this is you have no way of knowing what the embedded query name is that Access creates behind the scenes for the Access 2013 web app view. (It's actually a GUID name behind the scenes.)
I even had to add a special troubleshooting note in my book on this since I knew people would get tripped up on this (Page 584):
TROUBLESHOOTING Why do I get an error trying to use a Where clause with an OpenPopup or ChangeView action when the view is based on an embedded query? Access Services requires the Where clause to include the table or query name on which the view is based. When you define an embedded query as the record source for a view, Access Services creates a hidden system query that is not visible in the Navigation pane. Therefore, you cannot use a Where clause with the OpenPopup action or ChangeView action to open a view based on an embedded query. To work around this limitation, you can base your view on a saved query object. Note that Access Services creates a hidden system query as the record source also for Summary views. This means that you cannot use the Where clause argument to open a Summary view to a specific record or set of records. However, the workaround mentioned above won't work for Summary views.