Unable to edit record twice in linked SQL view in Access

0
votes

I have a SQL view created from normalized tables linked into Access. I created a form off of it to help control user access. I can make all the updates I want in the linked view, but - in the form - if I try to change a record I already updated I get the following error; "The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes."

Dirty is set to False and all tables that will update have a timestamp.

1
sqlvbams-access
I can probably give you a crude fix. However, if you want real help, provide a minimal reproducible example, including the SQL of that view, data type and defaults of the tables, all relevant properties (fetch defaults, recordset type, record locks, etc.) – Erik A
By a simple search for the first half of the message you will get answers too;) – ComputerVersteher
Thanks! Me.Form.Requery worked :) – Paige Haas
Maybe this covers the symptoms, but doesn't fix the wrong settings :( – ComputerVersteher

1 Answers

0
votes

Sounds like the auto form save event fires more than once. You might want to have more control over the update transaction, with the following:

  1. Do not link the form record source property to the table or query
  2. Use queries to load data: select based recordset as in Rst1 = dbCurr.OpenRecordset(); and update data using Rst1.update or action query DoCmd.RunSQL "UPDATE Query;"
  3. Test the timestamp field before you save the changes.

The cost is you will need a bit more code to transfer data from the recordset/query to the FormFields and viceversa; plus you need to build a save or update button to initiate the data save transaction.