Need to check if a record is locked in onCurrent() 1

[martinjsteven]

Help !

I need to display on a form whether the record you have moved to is locked by another user. I have "edited record" (pessimistic) locking on the form. Obviously you'd know it was locked because you wouldn't be able to edit any fields on the form, but the users of the database don't seem to grasp this concept. I therefore need to display a message that the record is locked on screen, so need to check in the onCurrent() event if this is the case, then display LOCKED if it is, and EDITABLE if it isn't.

Please help, my users are doing my head in !

Thanks

Martin

 

[ziwacky]

Hi,

Found this routine that may help.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim boolLocked As Boolean

Set cnn = New ADODB.Connection
cnn.ConnectionString = " your connection "
cnn.Open

Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
rst.CursorLocation = adUseServer
rst.Open "Authors", Options:=adCmdTable

boolLocked = IsItLocked(rst)
MsgBox boolLocked

End Sub

Function IsItLocked(rstAny As ADODB.Recordset) As Boolean
On Error GoTo IsItLocked_Err
IsItLocked = False

With rstAny
.Update
End With
Exit Function

IsItLocked_Err:
If Err = -2147467259 Then
IsItLocked = True
Exit Function
End If
End Function


Good Luck!

 

[martinjsteven]

Yes this code does work, but not in the context in which I have the form already - ie I have a form bound to a table which users can scroll back and forth using the record navigation buttons.

It needs to display for each record whether that record is locked when the user navigates to that record, therefore the only option is to use the onCurrent() event I think, and find some way of telling if the current record is already locked.

Thanks again

Martin

 

[Mossoft]

Maybe something like:

Function isRecordLocked(i_txtField As TextBox) As Boolean

On Error GoTo IRL_Error

isRecordLocked = False

i_txtField = i_txtField

Exit Function
IRL_Error:
Select Case Err
Case 3218, 3197, 3260
isRecordLocked = True
Case Else
Err.Raise Err.Number
End Select

End Function

In Words:
Pass a bound text box to the function which attempts to update the field with its own contents. If a record lock error occurs, return true, if another error occurs pass the error to the calling routine, otherwise return False

Haven't tried it.

M

 

[MichaelRed]

Interesting,

Mosoft - doesn't the act of attempting to update the (presumably BOUND) field generate the lock -and thus ALWAYS return "Locked"?

martinjsteven, I do not understand your issue w/ ziwacky's soloution. If you are NOT using continuious forms, call his routine from the OnCurrent event and use the result to set the caption of your label. Better yet, modify the routine to return a string and just set the label to the (return) value of the procedure.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over