Error 3218 - Record Locked

[MykelDL]

I searched for this error, and I'm surprised it hasn't been asked. I get an 3218 error when trying to update a record. Can I program around it? Yes. But I'd like to know if someone else has dealt with this issue in a way that is easily addressed. i.e., Solve the error, instead using the equivalent of "On error resume next."



Situation

1. I have to use SharePoint lists for my tables.
2. I am capturing who changes a record and when, including changes to linked records [e.g., If the name of a document (linked record) has been updated on a translation certificate. I want to capture who changed it and when as part of the translation certificate record.] Each translation certificate records has "UpdatedBy" and "Udpated" fields.
3. To stop people from making changes by mistake, the translation certificate form record is read-only. I use a pop-up edit form to make all changes. (e.g., status updates, changes to documents, changes to countries that receive the certificate, etc.)
4. Because getting data from SharePoint lists can be slow and most of the time people are only looking at the data (not changing or adding new), I originally set the form's data to Snapshot instead of Dynaset.

I created a module to let me update the parent record whenever any changes are made to it or any of the child records (documents and languages). I've got 5 of more forms that have similar construction, but relate to different types of records (translations, printing, shipping, etc.), so I was hoping I'd be able to consistently apply one module that does this update to the parent record. I pass the table name and the parent record ID to the module. It works exceptionally well in the DEV environment, where all the tables are local, but when I deploy the programming to the live frontend that is tied to the SharePoint list backend, I get the "3218: Could not update; currently locked" error.

Here's what I've tried to problem-solve to get rid of the error, unsuccessfully.

1. I've tried toggling between Snapshot and Dynaset.
2. I've played around with the various RecordLock settings.
3. By default, the database Option for Cache format compatible with 2010 and later was unchecked. I tried checking it and checking the Never Cache option.
4. I've tried writing the Update module as either a RecordSet --> .Edit --> .Update sequence or as executing an UPDATE SQL statement.
5. In the past, I closed the form, updated this type of data and reopened the form. I don't recall getting this error, but it's REALLY slow reopening because of SharePoint.
6. If I just put the fields getting updates on the pop-up Edit form and update the data before saving the record, I have no issues--it's just not elegant and needs to be done in all circumstances.
7. As I was typing this, it occurred to me that the form on which I first was getting this error is built on a query instead of a table--and my updates are just to one of those tables. I'll see if I can figure out if that's what is locking the record.

Thank you in advance for your time.

[Minty]

This sounds like the classic case of trying to update a record on one form that is already open on another.

Try making the main "listing form" a snapshot only, and the pop up editing form obviously needs to be a Dynaset.

Open the pop up in dialog form and in the code that opens it refresh the main listing forms recordset (not the form) to see the changes.
It's possible that the use of Sharepoint might introduce some kind of weird record locking that breaks the normal behaviour but this approach should work.

[MykelDL]

It's possible that the use of Sharepoint might introduce some kind of weird record locking that breaks the normal behaviour but this approach should work.

Thanks for the response, Minty. Clearly my description of the problem wasn't clear enough:

1. I do not have this problem except for when I transition my programming to the live FE, which uses the SharePoint lists.
2. The listing (read-only) form is/was "snapshot" and the pop-up editing is Dynaset.
3. I actually close the editing form and then run the update module to update the "Updatedby" and "Updated" data associated with the record in the "read-only" form, then requery that read-only form in order to show the updates. The code to execute the update is the last line of Edit form's "cmdSave" subroutine. I close the form then execute the module to update the data and requery the read-only form to show the updates. <--I think this may be the culprit. Maybe the data is kept locked until the sub finishes running, even if I've closed the edit form! Again, all of this works fine when the tables are local, or when running the module separately from the edit form's "cmdSave" subroutine. So the question is, how can I trigger the module to run after a successful update, but not as part of the edit form's sub?
4. 4. I should note that I can run the update module when using the SharePoint lists at any time using the Immediate Window, regardless of if the read-only form was in Snapshot or Dynaset. So it just cannot do so on the heals of closing the pop-up edit form.

I do believe this is a SharePoint list locking issue of some type, so I was hoping someone else would have experienced that and be able to share a way they dealt with it.