Lock Violations in Update Query

[Guest]

I have written an update query that will adjust the rank of items in a list
as follows:

Private Sub Rank_AfterUpdate()
Dim CurrentProcessID As Integer
Dim RankUpdate As String

CurrentProcessID = ProcessID

RankUpdate = "UPDATE tblProcessActions SET Rank = Rank + 1 WHERE ProcessID = "
RankUpdate = RankUpdate & CurrentProcessID & " AND Rank >= " & Rank
RankUpdate = RankUpdate & " AND Rank < " & Me.Rank.OldValue

DoCmd.RunSQL RankUpdate
Me.Requery
End Sub

Whenever I run the query, I get the following message:

Microsoft Access didn't update 0 field(s) due to a type conversion failure,
0 record(s) due to key violations, 5 record(s) due to lock violations, 0
record(s) due to validation rule violations.
Do you want to conyinue running this type of action query anyway?

Selecting yes does not update the records. There are no other users in the
database. Can you help me explain why I am having this problem?

 

[Michel Walsh]

Hi,



I would try a Me.Dirty=False or a Me.Undo (save or undo changes) before
running the DoCmd.RunSQL.


Have you a recordset with pending update? Do you have other users (piece
of running code is another user too) that play with the range of records to
be modified?


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

With some further adjustment, that works. Thanks for your help.