My application reads and updates data in a a MySQL DB via a MySQL 5.1 DSN. I can browse to a record directly via the linked table, make changes directly in the table, save the changes by moving to another record, move back, make more changes, and save the new changes. All continues to work fine when I make a change to a bound field on a bound form, close my form, and the record is saved. The new data is there when I look it up via my linked table and even if I look at the data directly in the MySQL DB via PHPMyAdmin (online management tool for MySQL databases).
However, when I then re-open the form, make another change, and again close the form, I get error 7787--data has been updated by another user. This error persists for the affected record even after I entirely close and re-open my Access app. In fact, I now get the same 7787 error when attempting to change any field in this record directly in the linked table.
I was able to "unlock" this by going directly to the SQL processor in PHPMyAdmin and running an update statement affecting the record.
As a test, I resorted to forcing a save of the record immediately after making the change on the form by constructing the SQL statement and running it through like this:
Dim strSQL as String
strsql = " UPDATE address SET latitude = 47.845123372972, longitude = -122.282912051305 WHERE address_id = 15"
CurrentDb.Execute strSQL, dbFailOnError
This works; however, I then have to cancel my changes on the bound form, or it generates the same 7787 error because the record has, indeed, been changed since I navigated to the current record.
I really want the simplicity of using a bound form here. Any ideas on what is happening here?