Err 3218, Could not update, currently locked Access 2007 Sharepo

[Dale Fye]

I've got an Access 2007 FE and a Sharepoint list as my BE. But I'm trying to
implement some record locking, so I've added a yes/no field (IsLocked) and a
LockedBy field to the BE table.

What I want to do is set these values as soon as the first user accesses a
particular page. That way, if another user pulls up that record, they are
informed immediately that the record is locked, and all the controls get
locked. Then, when the user moves off of a record, I want to release the
lock on that record (IsLocked = False, LockedBy = NULL) and set those fields
for the new record that is being accessed.

I'm not having a problem setting IsLocked and LockedBy when I get to a new
record, I just set the value of those controls as use me.dirty = false.
However, when I try to reset those values on the record I just left using an
update query, I get the 3218 error message. Code segment follows:

'If the users previous record is locked, then unlock it
If lngId <> 0 Then
strSQL = "Update tbl_POCs " _
& "SET IsLocked = False, Locked_By = NULL " _
& "WHERE [ID] = " & lngId
Set wrk = DBEngine.Workspaces(0)
wrk.BeginTrans
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
wrk.CommitTrans
lngId = 0
End If

I created a local table that mimics the one in the Sharepoint list, and
modified my form to point to that table. When I do that, I don't get this
problem. I originally didn't have the beginTrans and CommitTrans lines in
this code, but thought that might make a difference. But it doesn't appear
to make a difference

Anybody have any recommendations?
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Never did get an answer to this.

The problem was the when I went to a new record, using the form navigation
buttons, I did not get a chance to reset the IsLocked and LockedBy fields.
For some reason, Sharepoint was locking the previous record and would not
allow it to be updated using an update query (kept generating the 3218 error).

To bypass this, I created my own navigation buttons, and called a subroutine
from those that cleared the values (just set the control values and committed
the form) before moving to the new record.

Sure would be nice if there was an event that fired every time (whether the
form is dirty or not), before you move to the next record.

Dale
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.