record locks

[OD]

I need to check to see if a user has placed a editrecord lock on a record. Is
thier a good way to test for a lock. If I know that thier is a lock set then
I can do something. Right now I have the form set to no locks.


Thanks OD

 

[Kay Davies]

Hi OD
if you go into the design view of the form and bring up the properties of
the record that you are interested in (by right clicking whilst it is
highlighted) then you should be able to see whether it is set to read only.

hope this helps

 

[Ken Sheridan]

Optimistic locking (no locks) doesn't make a lot of sense these days. It was
popular before Access 2002 when Access could only use page locking, not
record locking as it avoided other records on the page being locked as well
as the record being edited. If pessimistic locking (edited record) is used,
provide the record selectors are shown on your form, if you try to edit a
record which is currently locked by another user then the record selector
will turn from the pencil symbol to a 'no entry' sign, and you won't be able
to edit it.

Ken Sheridan
Stafford, England

 

[OD]

Optimistic locking (no locks) doesn't make a lot of sense these days. It was
popular before Access 2002 when Access could only use page locking, not
record locking as it avoided other records on the page being locked as well
as the record being edited. If pessimistic locking (edited record) is used,
provide the record selectors are shown on your form, if you try to edit a
record which is currently locked by another user then the record selector
will turn from the pencil symbol to a 'no entry' sign, and you won't be able
to edit it.

Ken Sheridan
Stafford, England

I'm not using the record selector in my form. So with code I need to see if
this record is locked by onother user. I looked to something like islocked().
then I could use something like "if islocked() = true then do something endif"

Thanks OD

 

[Ken Sheridan]

No trappable error is raised if you try to edit the form. Access will beep
at the user, but that's all. You could perhaps try changing the value of a
bound control to itself in code and see if a runtime error is raised if the
record is locked by another user. If so you could then trap this error.

Ken Sheridan
Stafford, England

 

[OD]

No trappable error is raised if you try to edit the form. Access will beep
at the user, but that's all. You could perhaps try changing the value of a
bound control to itself in code and see if a runtime error is raised if the
record is locked by another user. If so you could then trap this error.

Ken Sheridan
Stafford, England

Is thier away to check to see if a recoord is lockeked at the record level
instead of at the form level. If the record selector is getting the info is
thier away to get it from the record selector.

By the way I'm new to using Access, I realy have not done too much
programing sence my Paradox for dos days, I under stood how Paradox Locks
worked. So I'm trying to get a handle on how Access does it.

thanks

 

[Ken Sheridan]

I know of no way of addressing the record selector.

You could try updating any non_Null column (i.e. a Required column) to
itself in a function like this:

Public Function IsLocked(strtable As String, _
strColumn As String, _
strKey As String, _
lngID As Long) As Boolean

Const conRECORDLOCKED = -2147467259
Dim cmd As ADODB.Command
Dim strSQL As String

IsLocked = False

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' attempt to update row in table
strSQL = "UPDATE(" & strtable & ")" & _
" SET " & strColumn & " = " & strColumn & _
" WHERE " & strKey & " = " & lngID
cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
Select Case Err.Number
Case 0
' no error
Case conRECORDLOCKED
' record is locked
IsLocked = True
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

End Function

This seems to work if called from code in a standard module, e.g.

Debug.Print IsLocked("Contacts","Lastname","ContactID",2)

but not if called in a form's class module and the value of the current
record's primary key is passed to the function, e.g.

Dim lngID As Long

lngID = Me.ContactID

If IsLocked("Contacts","Lastname","ContactID", lngID) Then
MsgBox "Record is locked."
End If

in which case the IsLocked function returns True for every record. Even if
the values are passed to a procedure in a standard module and then to the
function the same happens. I expect there's a rational explanation, but It
makes no sense to me, I'm afraid.

Ken Sheridan
Stafford, England

 

[OD]

I know of no way of addressing the record selector.

You could try updating any non_Null column (i.e. a Required column) to
itself in a function like this:

Public Function IsLocked(strtable As String, _
strColumn As String, _
strKey As String, _
lngID As Long) As Boolean

Const conRECORDLOCKED = -2147467259
Dim cmd As ADODB.Command
Dim strSQL As String

IsLocked = False

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' attempt to update row in table
strSQL = "UPDATE(" & strtable & ")" & _
" SET " & strColumn & " = " & strColumn & _
" WHERE " & strKey & " = " & lngID
cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
Select Case Err.Number
Case 0
' no error
Case conRECORDLOCKED
' record is locked
IsLocked = True
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

End Function

This seems to work if called from code in a standard module, e.g.

Debug.Print IsLocked("Contacts","Lastname","ContactID",2)

but not if called in a form's class module and the value of the current
record's primary key is passed to the function, e.g.

Dim lngID As Long

lngID = Me.ContactID

If IsLocked("Contacts","Lastname","ContactID", lngID) Then
MsgBox "Record is locked."
End If

in which case the IsLocked function returns True for every record. Even if
the values are passed to a procedure in a standard module and then to the
function the same happens. I expect there's a rational explanation, but It
makes no sense to me, I'm afraid.

Ken Sheridan
Stafford, England

Thanks Ken,

I had to change some of the things that I do in the forms, this project is
just for 5 users, and I can live with the problem. One of the problems that I
had was, I don't like the way Access stores the data in a table. I was having
to use ucase([field name]) to make sure the the data was stored in upper
case. I don;t like just using the format > command, because it may be
displayed in uppercase its not stored that way. Its not a big problem, it
just bugs me.

Thanks agian for the help.

OD

 

[Ken Sheridan]

You can force input in a control in a form to upper case by putting the
following procedure in a standard module:

Public Sub ConvertToCaps(KeyAscii As Integer)
' Converts text typed into control to upper case

On Error GoTo Err_Handler

Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_Here

End Sub

Then in the KeyPress event procedure of any control whose text you want
forced to upper case put:

ConvertToCaps KeyAscii

Note that you must call the procedure in the event procedure. You cannot
make it a function and call it directly from the control's properties sheet
as you normally would when using a function as an event property.

Ken Sheridan
Stafford, England

 

[OD]

Thanks Ken
That was a fast response, I think that will help with one of my problems.

Thanks
OD

You can force input in a control in a form to upper case by putting the
following procedure in a standard module:

Public Sub ConvertToCaps(KeyAscii As Integer)
' Converts text typed into control to upper case

On Error GoTo Err_Handler

Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_Here

End Sub

Then in the KeyPress event procedure of any control whose text you want
forced to upper case put:

ConvertToCaps KeyAscii

Note that you must call the procedure in the event procedure. You cannot
make it a function and call it directly from the control's properties sheet
as you normally would when using a function as an event property.

Ken Sheridan
Stafford, England

Thanks Ken,

I had to change some of the things that I do in the forms, this project is
just for 5 users, and I can live with the problem. One of the problems that I
had was, I don't like the way Access stores the data in a table. I was having
to use ucase([field name]) to make sure the the data was stored in upper
case. I don;t like just using the format > command, because it may be
displayed in uppercase its not stored that way. Its not a big problem, it
just bugs me.

Thanks agian for the help.

OD

 

[OD]

Ken Thanks,

I just tried the convertToCaps, I works great, now I will fix my forms.

Thanks a lot it really helped.

OD

Thanks Ken
That was a fast response, I think that will help with one of my problems.

Thanks
OD

You can force input in a control in a form to upper case by putting the
following procedure in a standard module:

Public Sub ConvertToCaps(KeyAscii As Integer)
' Converts text typed into control to upper case

On Error GoTo Err_Handler

Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_Here

End Sub

Then in the KeyPress event procedure of any control whose text you want
forced to upper case put:

ConvertToCaps KeyAscii

Note that you must call the procedure in the event procedure. You cannot
make it a function and call it directly from the control's properties sheet
as you normally would when using a function as an event property.

Ken Sheridan
Stafford, England

Thanks Ken,

I had to change some of the things that I do in the forms, this project is
just for 5 users, and I can live with the problem. One of the problems that I
had was, I don't like the way Access stores the data in a table. I was having
to use ucase([field name]) to make sure the the data was stored in upper
case. I don;t like just using the format > command, because it may be
displayed in uppercase its not stored that way. Its not a big problem, it
just bugs me.

Thanks agian for the help.

OD