Update with Inner Join - Mystery Error Message

[dmbaer]

I have the following simple update statement in an Access (2021) query:

UPDATE Patrons INNER JOIN PatronsWork ON Patrons.PatronID=PatronsWork.PatronID SET Patrons.Zip = PatronsWork.Zip;

I've found numerous examples of update statements exactly like it on various web sites, and can think of no way to simplify it further. When I run it, I'm always getting the following error:

"An argument to the Updated function was invalid. The field name must be provided as a string value enclosed in quotation marks."

Just to be clear, none of the field names in either table contain anything but upper and lower case letters. Also, adding brackets ( [Patrons].[Zip] ) makes no difference.

I've been banging my head on this wall for hours. Any help would be massively appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

The mention of the "Updated" function implies to me there may be a trigger macro attached to the table. Could you please verify that?

 

[dmbaer]

A very long time ago, I added a before-change action to update a last-changed-date field. I can see this when I click Rename/Delete Macro on the Table Design tab - that is, it tells me one is there. For the life of me, I don't know how to get to that macro or find out what it's even named. However, you may be on to something.

Edit: Actually, I found it after all. It sets a create-date value in one field on insert, and sets a changed-date field under other conditions.

 

[theDBguy]

A very long time ago, I added a before-change action to update a last-changed-date field. I can see this when I click Rename/Delete Macro on the Table Design tab - that is, it tells me one is there. For the life of me, I don't know how to get to that macro or find out what it's even named. However, you may be on to something.

You can go to the table's Design View to see those macros.

 

[dmbaer]

Yes, our messages crossed. I poked around and found two places to see the before-change processing. I do not, however, see anything that looks questionable - not that I really know what I should be looking for. In addition, prior to my attempt to run the update mentioned above, I've never had a hint of problems with this processing. The timestamp field updates happen exactly as desired.

 

[theDBguy]

Yes, our messages crossed. I poked around and found two places to see the before-change processing. I do not, however, see anything that looks questionable - not that I really know what I should be looking for. In addition, prior to my attempt to run the update mentioned above, I've never had a hint of problems with this processing. The timestamp field updates happen exactly as desired.

Just as a troubleshooting step, try disabling those macros for a minute and then execute your code to see if the error goes away. Just a thought...

 

[dmbaer]

Eureka!!!! You pointed me in exactly the right direction. I had changed a couple of field names in the table that were referenced in the before-change macro. The mystery message has now gone away.

Have a massive helping of good karma. I'm doing this work for a non-profit who will really appreciate having the upgrades I'm attempting. So, good on you, generous sir!

 

[theDBguy]

Eureka!!!! You pointed me in exactly the right direction. I had changed a couple of field names in the table that were referenced in the before-change macro. The mystery message has now gone away.

Have a massive helping of good karma. I'm doing this work for a non-profit who will really appreciate having the upgrades I'm attempting. So, good on you, generous sir!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.