Update query not running due to lock violation

I have just published an Access file to SharePoint and am unable to run an update query. I know most on here don't really like SharePoint, but there must be a workaround for my issue.

I get a message saying that it's unable to update all of the records due to lock vialations. I've tried moving to the next record before running the update query, but even that does not work. The query runs just fine if I run it manually.

 
Private Sub TargetShare_AfterUpdate()

If IsNull(Me.TargetShare) Then
Me.UnitGoal = Me.Share2011 * Me.CropAcres2012 / Me.PltRate
Else
Me.UnitGoal = Me.TargetShare * Me.CropAcres2012 / Me.PltRate
End If

DoCmd.OpenQuery "qryUpdateForTargetShare"

Me.Parent!frmLocationGoals.Requery
End Sub

Open in new window

Dale LoganConsultantAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
The error is probably occurring because the query is trying to access the current record (which you are still in, hence it is locked)

Try the code like this:
Private Sub TargetShare_AfterUpdate()

If IsNull(Me.TargetShare) Then
Me.UnitGoal = Me.Share2011 * Me.CropAcres2012 / Me.PltRate
Else
Me.UnitGoal = Me.TargetShare * Me.CropAcres2012 / Me.PltRate
End If
docmd.runcommand accmdsaverecord
doevents
DoCmd.OpenQuery "qryUpdateForTargetShare"
doevents
Me.Parent!frmLocationGoals.Requery
End Sub


Or like this, perhaps to close the form then reopen it:

Private Sub TargetShare_AfterUpdate()

If IsNull(Me.TargetShare) Then
Me.UnitGoal = Me.Share2011 * Me.CropAcres2012 / Me.PltRate
Else
Me.UnitGoal = Me.TargetShare * Me.CropAcres2012 / Me.PltRate
End If
docmd.close
DoCmd.OpenQuery "qryUpdateForTargetShare"
docmd.openform "YourFormNameHere"

End Sub


Just curious, ...
Whats the reason why the query can't be run when the DB opens, or after the last editing session?
Thus possibly avoiding the need to do this after *Every* record changes...


JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Typically a (Bulk) update query is run on it's own, and not each time a record is updated...

So can you first explain exactly what this system is trying to do?

Then also post the SQL for qryUpdateForTargetShare

JeffCoachman
0
 
Dale LoganConsultantAuthor Commented:
It's a forecasting tool. Next years sales goals are forecast using last years market share. Sales goals are created at the location level and that number is allocated to the product level using last years mix of products sold. The tool allows the user to override last years market share value for a location with a target share. This is where the update query comes in. When a user edits [TargetShare] the sales goal for the location is changed and the update query re-allocates that goal down to the product level.

Here's the SQL for qryUpdateForTargetShare
 
UPDATE tblGoals INNER JOIN tblSalesManufacturer ON (tblGoals.Crop_ID = tblSalesManufacturer.Crop_ID) AND (tblGoals.Location_ID = tblSalesManufacturer.Location_ID) SET tblSalesManufacturer.[2012UnitsGoal] = IIf(IsNull([TargetShare]),[2011Share]*[2012CropAcres]/[PltRate],[TargetShare]*[2012CropAcres]/[PltRate])*([tblSalesManufacturer].[2011UnitsSold]/[tblGoals].[2011UnitsSold])
WHERE (((tblSalesManufacturer.Crop_ID)=[Forms]![frmMain]![cmbCrop]) AND ((tblSalesManufacturer.Location_ID)=[Forms]![frmMain]![frmLocationSummary].[Form]![EditLocation]));

Open in new window

0
 
Dale LoganConsultantAuthor Commented:
Will try later.

They don't change the values very often. However, when they do, they want to see the updated values.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then I would run the SQL when the DB opens...

Create a vba code module
In the module, create a public function like this:

Public Function UpdateForTargetShare()
    DoCmd.OpenQuery "qryUpdateForTargetShare"
End Function

Create an "Autoexec" macro (the name MUST be "Autoexec" exactly, so that it opens automatically)
Action: Run Code
Function Name: UpdateForTargetShare()

So you have a few options here...

Keep me posted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.