Problem: Two tables are joined in a query (one-to-many) but I can not update a field in the "many" table. This appears to be a common problem reported in stackoverflow, but none of the posted solutions corrected my problem.

I have two tables: CustomerItems ; Items

Both tables have a text field called ItemID.

ItemID in the Items table is "indexed, no duplicates allowed". The ItemID in the CustomerItems table can have duplicate values.

I joined these two tables in a query as follows: "Include all records from 'CustomerItems' and only those records from 'Items' where the joined fields are equal.

I use this query to manually (I type in new info from keyboard) update a field in the CustomerItems table called NewInfo.

This table has worked for years but today it will not let me update the NewInfo field. I can update NewInfo if the tables are not joined. I checked to make sure there are no null values in the ItemID field in either table. I also double checked to make sure I did not have any duplicate values in the Items.ItemID field.

I'm not running an update query, I'm simply reviewing the data then manually entering values into the NewInfo field. I'm not getting any error messages, but Access does not allow me to enter any info from the keyboard.

Both tables contain a lot of other fields besides those named above, but they are not involved in this query.

I'm really stumped and would appreciate any ideas. Thanks

CodePudding user response：

It should work. It not a great idea to do this, but it can work. A common failure of course is if you adding a new record. If you don't save the record, then no autonumber PK id is generated, and thus child row/records can't be edited at that point in time.

So, this means that the "main" or so called parent record has to exist, be saved, and the autonumber PK value has been also generated. If this has not occurred, then child records of that same query as a general rule can't be changed or edited until such time a valid parent record has been created, and saved.

CodePudding user response：

I get this issue sometimes. First I try to Compact and Repair and that works sometimes. Otherwise, the solution that has always worked for me is to remove that table from Access and re-link/re-import it and don't forget to specify the PK! I was making this mistake of not specifying the PK until one day I accidentally selected a PK and it worked!