Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update query not running due to lock violation

Posted on 2011-10-24
5
Medium Priority
?
1,745 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Dale Logan
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37020652
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
 

Author Comment

by:Dale Logan
ID: 37024109
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37025055
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
 

Author Comment

by:Dale Logan
ID: 37027247
Will try later.

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

Expert Comment

by:Jeffrey Coachman
ID: 37027456
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question