Microsoft Access: Unable to update a recordset or field value

Microsoft Access is a seriously weird product, but I mostly know my way around the bugs. One of the most annoying bugs/behaviors is when Access decides you can't edit a field in a join. I can find neither rhyme nor reason for why this happens. This note has my entire, painfully acquired, solution set for this problem. It dates from Access 2000, but it remains topical

This recordset is not updateable

Access 2000 and Jet 4.0 changed quite a bit. One problem is that RecordSets that used to be updateable aren't.

As a fix, try changing the value of the query's UniqueRecords property (from yes to no or vice-versa) Q207761 - ACC2000 Changes in Use of DISTINCTROW in Microsoft Access 2000. See also 011217_AccessUpdatesHelpFile.pdf. I had to change the properties for the query to "Dynaset (Inconsistent Updates)", which I fear increases the risk of the Cursor Problem [1]. It's probably better to change the UniqueRecords property to NO.

Sometimes defining relationships in Tools:Relationship will prevent this from happening, especially if one assigns referential integrity and "cascade update related fields". Also look at which "side" of two joined fields appears in the relation, sometimes changing that will fix the problem.

[1] I think this was fixed in Access 2003. In Access 2000 it was possible for edits to be applied to rows removed from the row the UI presented.