Update Query Lock Violation (1 Viewer)

mdex

Registered User.
Local time
Today, 02:55
Joined
Jul 31, 2013
Messages
28
Please bear with me while I try and explain this...

My tables are linked sharepoint lists.

I have a form with 2 subforms showing in datasheet view. They each show data from the same table (Exceptions) with slightly differing criteria.

I have a column in each table that is a tickbox which is unticked by default. I don't have any record locks on either of the subforms but do allow edits to allow tickbox to be checked.

I have an update query that looks for any records which are ticked and it updates a name column. The name column is added via a dlookup to another table (Members) using a text box value from the main form.

The issue I'm facing is if I tick only 1 box and run the update query I get a lock violation. If I tick 2 or more boxes and run the update query the last record ticked gives the lock violation but the other 2 are updated ok.

I've tried to set focus to a text box on the form before running the update query but have the same issue. Any idea?
 

Minty

AWF VIP
Local time
Today, 09:55
Joined
Jul 26, 2013
Messages
10,355
Have you tried saving the record before running the update query ?

Code:
If Me.Dirty Then Dirty = False
rest of your update here....
 

mdex

Registered User.
Local time
Today, 02:55
Joined
Jul 31, 2013
Messages
28
Have you tried saving the record before running the update query ?

Code:
If Me.Dirty Then Dirty = False
rest of your update here....

Ok, now my button click has the following code.

Code:
If Me.Dirty Then Dirty = False
DoCmd.OpenQuery "AssignToMeUpdate"

It gives the same lock violation. I don't quite understand the dirty property, would I not have to check the dirty status of the subform rather than the main or does the main recognise changes in the sub and set it's own dirty property?
 

Minty

AWF VIP
Local time
Today, 09:55
Joined
Jul 26, 2013
Messages
10,355
Sorry yes , it's the subform you need to check. I didn't realise the button was on the Main form.

You could simply save the record in the after updated event of the check box?
 

mdex

Registered User.
Local time
Today, 02:55
Joined
Jul 31, 2013
Messages
28
Sorry yes , it's the subform you need to check. I didn't realise the button was on the Main form.

You could simply save the record in the after updated event of the check box?

I don't think I have explained this very well. A picture might be better. My tickbox is part of the table being shown via the subform. I want to be able to use the tickbox to select multiple records, press the assign button and those records which have a tick against them are modified using the update query.

 

Minty

AWF VIP
Local time
Today, 09:55
Joined
Jul 26, 2013
Messages
10,355
Okay - got it... It's only the last record you updated that isn't saved.
Forcing the subform to save from the main form;

Me.MySubform.Form.Dirty = False.

The dirty property is set to true if a records underlying data has changed.
Forcing it to false saves the changes.
 

mdex

Registered User.
Local time
Today, 02:55
Joined
Jul 31, 2013
Messages
28
Okay - got it... It's only the last record you updated that isn't saved.
Forcing the subform to save from the main form;

Me.MySubform.Form.Dirty = False.

The dirty property is set to true if a records underlying data has changed.
Forcing it to false saves the changes.

Ok, now I'm getting frustrated. Everything I've read suggests the solution you've given is sound and should work but it's still giving the lock violation on the last clicked text box.

I've even gone as far as to set every single form/subform as dirty and same issue. If I fully close the form and then reopen it and press the button it works as intended.

I'm sure it's something I've not thought of to tell you why it's not working. Could the tables being sharepoint lists be a problem?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:55
Joined
Feb 28, 2001
Messages
27,001
Lock violations can occur if somehow you have the table open and the locks are set to a restrictive setting. If, for example, the main form has a JOIN type query involving table X and the sub-form has a different query ALSO involving table X, and if the main form could try to update the underlying tables, then it might be possible that the sub-form could not. This is a form of deadlock.

The simplest solution is to sometimes change the data sources to use optimistic locking, which means they take out a restrictive lock on the source data only for as long as it takes to actually do the update. It doesn't close the window for interference - but it lowers the size of the window considerably.
 

mdex

Registered User.
Local time
Today, 02:55
Joined
Jul 31, 2013
Messages
28
So it's definitely SharePoint list related. As a test I converted to a local table and the function works as described.
 

Minty

AWF VIP
Local time
Today, 09:55
Joined
Jul 26, 2013
Messages
10,355
I wonder if having (effectively) the same data on the two subforms at the same time is maybe an issue.

Maybe the way sharepoint locks records is "less optimistic" ?
 

neilmod

New member
Local time
Today, 05:55
Joined
Apr 7, 2021
Messages
1
I came across this thread while searching for a solution to a similar problem. (My database is also built on SharePoint lists.) I've figured out a simple solution that works for me: Reassign the recordsource for the subform before running the problematic query:

Me.[Subform Control Name].Form.RecordSource = "[Existing RecordSource]"

You can use the same recordsource it's already using, but the action of reassigning it eliminates the record lock issue.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:55
Joined
Feb 28, 2001
Messages
27,001
Thanks, @neilmod. In a way that makes sense. Forcing a new recordsource (even if it is the same one) forces a "flush" of pending update buffers. If what was hanging up everything was a buffer, it makes sense. We have to remember that Windows uses a technique called read-ahead/write-behind for buffer management. (This is as opposed to always do reads and writes IMMEDIATELY, holding up everything while waiting for the device I/O to finish.) If the SharePoint lock is still engaged because a buffer is pending, that solution would force a buffer flush before allowing the recordsource change. The completion routine for the forced (flushed) I/O would release buffer locks - and because it is the same source, would take out new locks identical with the prior locks. BUT in taking out the locks, it has to go through lock resolution anew. Somewhere in there, the locks get re-evaluated and the problem gets resolved. I know I said that awkwardly, but I think that is what is going on.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:55
Joined
Feb 19, 2002
Messages
42,981
The actual problem is that you are conflicting with yourself. It is poor practice to run an update query to update a table bound to an open form.
 

sahithi

New member
Local time
Today, 15:25
Joined
May 27, 2021
Messages
1
Hi all,
Can someone help me out on same issue. Any help is much appreciated.

So What I was trying is to have update query executed (like call "after update" sub of textbox a) and fill form textbox b, when form textbox a is updated...
Though update query has joins with 2 linked lists, my assumption first form field a update locking the record .... so due to this update query in side "after update sub" is throwing error ....
But can some one help me out to figure this? If any better any way to avoid this error?
Have tried me.dirty= false still no suppression of this popup.
 

Users who are viewing this thread

Top Bottom