Record Locking - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Record Locking

Michael Medici

2 months ago

Hi all,

Hoping for a little help on a weird problem I am seeing. I have a continuous form and one of the fields is 'Status'. I have a button to Approve all which runs an update query that for any record where Status="Submitted", to change it to "Approved". I am finding that many times it will work fine with all but one record on the list and I am getting an error that the record is locked. I do not have record locking enabled at all in the form so not sure what is causing the 1 record to not release.

	Sami Shamma @Reply 2 months ago
Michael hi Run it again and see if it happens when you are sitting on one of those records in the continuous form

Donald Blackwell @Reply

2 months ago

Does your button save/refresh the records before running the query? For example, have you made sure that the last record you were on is not still "dirty". Access by default locks any record that is currently in use "record level locking".

Your button should have one of the following in its click event (if using VBA - if using macros, it will be similar):

Details

Me.Dirty = False

Me.Refresh

Me.Requery

Me.Recordset.Requery

Any one of those would save the current record before running queries. If it still gives you a locked, then we'll need to dig deeper.

	Michael Medici OP @Reply 2 months ago
@Sami - I've tried running the update query again but still no change and I have an edit button to open the selected record and from there I can update the status correctly and it holds. @Donald. Tried that before running the update query and still no change. I should mention, the tables are sharepoint lists if that matters.

Michael Medici OP @Reply

2 months ago

I found the problem. So stupid. I have a msgbox as part of the flow and for the msgbox I set the title="Submit". Well, there is a field in the sharepointlist called title that gets created when you migrate native access table to sharepoint and I guess when I tried to set the msgbox title, it actually went to the record in sharepoint and added submit to the title field, thus opening the record and locking it.. SMH

	Donald Blackwell @Reply 2 months ago
Ahh, yup, have had little things like that happen for me before as well. Glad you found the Gremlin :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View