Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-13-2019, 03:15 AM   #1
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 28
Thanks: 8
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Update Query Lock Violation

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?

mdex is offline   Reply With Quote
Old 03-13-2019, 03:32 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,082
Thanks: 143
Thanked 1,636 Times in 1,608 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Update Query Lock Violation

Have you tried saving the record before running the update query ?

Code:
If Me.Dirty Then Dirty = False
rest of your update here....
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-13-2019, 03:52 AM   #3
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 28
Thanks: 8
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Update Query Lock Violation

Quote:
Originally Posted by Minty View Post
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?

mdex is offline   Reply With Quote
Old 03-13-2019, 04:18 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,082
Thanks: 143
Thanked 1,636 Times in 1,608 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Update Query Lock Violation

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?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-13-2019, 04:26 AM   #5
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 28
Thanks: 8
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Update Query Lock Violation

Quote:
Originally Posted by Minty View Post
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.

mdex is offline   Reply With Quote
Old 03-13-2019, 06:08 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,082
Thanks: 143
Thanked 1,636 Times in 1,608 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Update Query Lock Violation

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
mdex (03-13-2019)
Old 03-13-2019, 06:27 AM   #7
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 28
Thanks: 8
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Update Query Lock Violation

Quote:
Originally Posted by Minty View Post
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?

mdex is offline   Reply With Quote
Old 03-13-2019, 06:46 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,114
Thanks: 69
Thanked 1,372 Times in 1,264 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Update Query Lock Violation

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 03-15-2019, 02:35 AM   #9
mdex
Newly Registered User
 
Join Date: Jul 2013
Posts: 28
Thanks: 8
Thanked 0 Times in 0 Posts
mdex is on a distinguished road
Re: Update Query Lock Violation

So it's definitely SharePoint list related. As a test I converted to a local table and the function works as described.
mdex is offline   Reply With Quote
Old 03-15-2019, 02:48 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,082
Thanks: 143
Thanked 1,636 Times in 1,608 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Update Query Lock Violation

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" ?

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
#DELETED# - Can't figure out why certain records won't delete. Lock Violation heygoena Queries 1 07-28-2011 01:50 AM
Update Query Key Violation wtrimble Queries 3 08-11-2010 10:46 AM
Primary Key Violation in Update Query DocNice Queries 3 04-25-2006 01:50 PM
Update/Append Query key violation kbrown Queries 2 09-10-2004 10:06 AM
Update query lock violations PeterWieland Queries 3 02-11-2003 02:26 AM




All times are GMT -8. The time now is 06:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World