record locks

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" wrote:

> 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
 >> Stay informed about: record locks 

"Ken Sheridan" wrote:

> 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" wrote:
>
> > 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
>
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
 >> Stay informed about: record locks 

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" wrote:

>
>
> "Ken Sheridan" wrote:
>
> > 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" wrote:
> >
> > > 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
> >
> 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
 >> Stay informed about: record locks 

"Ken Sheridan" wrote:

> 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" wrote:
>
> >
> >
> > "Ken Sheridan" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > >
> > 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
>
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
 >> Stay informed about: record locks 

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" wrote:

>
>
> "Ken Sheridan" wrote:
>
> > 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" wrote:
> >
> > >
> > >
> > > "Ken Sheridan" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
> > > >
> > > 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
> >
> 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
 >> Stay informed about: record locks 

"Ken Sheridan" wrote:

> 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" wrote:
>
> >
> >
> > "Ken Sheridan" wrote:
> >
> > > 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" wrote:
> > >
> > > >
> > > >
> > > > "Ken Sheridan" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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
> > > > >
> > > > 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
> > >
> > 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
>
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
 >> Stay informed about: record locks 

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" wrote:

> 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
 >> Stay informed about: record locks 

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

Thanks
OD

"Ken Sheridan" wrote:

> 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" wrote:
>
> > 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
>
 >> Stay informed about: record locks 

Ken Thanks,

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

Thanks a lot it really helped.

OD

"OD" wrote:

>
> Thanks Ken
> That was a fast response, I think that will help with one of my problems.
>
> Thanks
> OD
>
> "Ken Sheridan" wrote:
>
> > 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" wrote:
> >
> > > 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
> >
 >> Stay informed about: record locks