asked on

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

Jeffrey Coachman

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

Dale Logan

ASKER

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

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Logan

ASKER

Will try later.

They don't change the values very often. However, when they do, they want to see the updated values.

Jeffrey Coachman

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