• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1699
  • Last Modified:

microsoft access vba code to see if a record is being edited in a multiuser environment.

I have a form called frmtest and a table called test that this form is used to read/write data to the table test.  I wanted to know if there is a way to use VBA code to determine if a user is editing that particular record or not.  Right now I am using Access 2002 db that opens the db using record level locking and this db is used in a multiuser environment.  If I go into the form's design view and look at the properties, the locks setting is set to no locks.  I can change it to EditedRecord, but the user gets a circle slash sign on the record selector with the beeping sound from the keyboard indicating that the record is locked.  However, this comes with no messages.  So I want to be able to test if a record is being edited and if a second user attempts to edit, the user gets a message "This record is currently locked for editing.  Come back later."  How can VBA accomplish this?
1
sxxgupta
Asked:
sxxgupta
  • 9
  • 4
  • 4
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 You would think their would be a property for this, but there is not.  The only way to determine if there is a lock or not is to attemp it yourself and see if you get an error as done in the attached code.

Jim.

 
Function IsLocked(rs As Recordset, UserName As String, MachineName As String)
        ' Accepts: a recordset and two string variables
        ' Purpose: determines if the current record in the recordset is locked,
        '          and if so who has it locked.
        ' Returns: True if current record is locked (and sets UserName
        '          and MachineName to the user with the lock).  False if the
        '          record isn't locked.
        ' From: Building Applications Chapter 12

        Dim ErrorString As String
        Dim MachineNameStart As Integer

10      IsLocked = False
20      On Error GoTo IsLockedError
30      rs.Edit                       'Try to edit the current record in the recordset.
40      rs.MoveNext
50      rs.MovePrevious
60      Exit Function                 'No error, so return False.

IsLockedError:
70      If Err = 3260 Then            'Record is locked -- parse error string.
80        ErrorString = Error$
90        UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
100       If UserName = "" Then UserName = "(unknown)"
110       MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
120       MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
130       If MachineName = "" Then MachineName = "(unknown)"
140       IsLocked = True
150     End If
160     Exit Function

End Function

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
But JET typically locks a page of records, so I don't think this is completely accurate relative to a specific record.  On the other hand, I guess it's doesn't matter because the record is still locked and can't be edited.

mx
0
 
sxxguptaAuthor Commented:
ok will try.........

Where do I call this function in my form and is the syntax for calling it (sorry....I am a novice at this) and do I change the record locks property of the form to No Locks vs. Edited Record?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"and do I change the record locks property of the form to No Locks vs. Edited Record?"

No ... in any place you can, set to No Locks.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Edited Record is a bit misleading ... review the KB's below to get a picture on the situation:

Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60
http://support.microsoft.com/kb/306435

Page-Level Locking vs. Record-Level Locking
http://msdn.microsoft.com/en-us/library/aa189633(office.10).aspx 

Record-Level Locking Does Not Appear to Work
http://support.microsoft.com/kb/225926

Handling Locking Conflicts
http://msdn.microsoft.com/en-us/library/aa165294%28office.10%29.aspx 

mx
0
 
sxxguptaAuthor Commented:
ok....but how do I use this function or where do I use this function.  In my form's current event and what is the syntax to call this function in my form's current event?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm not really sure, mainly because I'm not sure what the arguments to be passed are ...?
JDettman will have to fill you in ..

mx
0
 
sxxguptaAuthor Commented:
Thanks mx.  Insightful articles.  Just not sure how to use this function.  After I know how to use this function, I will test it in a multiuser environment.

JD:
the table is called test and my form's name is frmtest.  The table has one primary autonumber key called ID.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 First in regards to no locks vs Edited record, understand that this controls when the lock is placed.  With Edited record, a lock is placed as soon as the user starts to change the record.  It's not released until the save is complete.  With no locks, it's only placed at the time of save for the duration of the save.  It does not mean that there is no locking taking place, just that there is no Edit lock being used.

  The locking level, record or page, is seperate.

  So with this type of logic (warning the user the record is being edited), you would want Edited record and row level locking.  However note that this may give you concurrency issues.  As MX has pointed out, row level locking is not all that its cracked up to be.

  Even though selected, you will still get page level locking with some operations, such as index updates.  Also long value pages, which store OLE and memo fields, will always be page locked.

  There are two alternatives to that:

1. Padding records - JET will never split a record across pages, so by making a record bigger then a half page, you will end up with one record per page and "record level locking" even though page locking is in effect as far as JET is concerned.

   This technique is nice in that you can control it table by table.

2. psuedo locking - You leave JET set for page locking and use No Locks on forms.  Then outside of that, keep track of your own locks.  That can be done with the procedures I describe here:

Resource locking in your applications

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_5328-Resource-locking-in-your-applications.html

   It's more work, but more controllable in terms of when locks get placed.  

  As for where the code is placed to check if a record is locked, OnCurrent would work, but at that point you don't know if the user is going to edit the record or not.  Typically you'll find this code attached to either a search function or an Edit button or possible in the OnCurrent of a popup form (from a search page, you popup an "edit" form after they select a record).

Jim.
0
 
sxxguptaAuthor Commented:
Hi JD:

Ok will look into it.  I have an unbound text boxt called txtSearch (custom navigation) on the form.  After the user enters a value in this (after update event of the txtSearch box) and as long as me.lockactive<>1 the appropriate record is shown, and I then set me.lockactive (textbox) to 1, which then shows the pencil marker that the record is in edit mode.  So now if another user types in the same value in the txtSearch box and attempt to go to that record, I want access to flag this and say that another user has this record in edit mode.
0
 
sxxguptaAuthor Commented:
JD:
How do I "pad" a record?
0
 
sxxguptaAuthor Commented:
Wow, that was great.  Just not sure how I am going to use it being a novice vba user.  I guess the first option of creating a table and tracking locked records would be a start.  Again not sure how to "apply" it.
test.mdb
0
 
sxxguptaAuthor Commented:
JD:

I am just trying to understand how to syntactically apply your function below.  I have attached a very simple form and table and could really use your insight cause you obviously know how to do this programatically?
Function IsLocked(rs As Recordset, UserName As String, MachineName As String)
        ' Accepts: a recordset and two string variables
        ' Purpose: determines if the current record in the recordset is locked,
        '          and if so who has it locked.
        ' Returns: True if current record is locked (and sets UserName
        '          and MachineName to the user with the lock).  False if the
        '          record isn't locked.
        ' From: Building Applications Chapter 12

        Dim ErrorString As String
        Dim MachineNameStart As Integer

10      IsLocked = False
20      On Error GoTo IsLockedError
30      rs.Edit                       'Try to edit the current record in the recordset.
40      rs.MoveNext
50      rs.MovePrevious
60      Exit Function                 'No error, so return False.

IsLockedError:
70      If Err = 3260 Then            'Record is locked -- parse error string.
80        ErrorString = Error$
90        UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
100       If UserName = "" Then UserName = "(unknown)"
110       MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
120       MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
130       If MachineName = "" Then MachineName = "(unknown)"
140       IsLocked = True
150     End If
160     Exit Function

End Function

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<How do I "pad" a record? >>

  A page is 4096 bytes and text is stored unicode (2 bytes per character), so you need more then 2048 bytes of storage in the record.  You can estimate the record size by adding up the fields plus overhead:

1. Ten bytes per record for record overhead.

2. One byte variable-length field overhead for each Text, Memo, Hyperlink, and Long Binary (OLE Object) field.

3. One additional byte for every 256 bytes of the total space occupied by all Text and Hyperlink fields

4. One byte fixed-length field overhead for each Yes/No, byte, Integer, Long Integer, AutoNumber, Single, Double, Currency, and Date/Time field

For the fields:

Byte 1 byte
Integer 2 bytes
Long Integer 4 bytes
Single 4 bytes
Double 8 bytes
Currency 8 bytes
AutoNumber with FieldSize property set to Long Integer Long Integer 4 bytes
AutoNumber with FieldSize property set to ReplicationID GUID 16 bytes
Yes/No Boolean 1 bit
Date/Time 8 bytes
Text Variable
Memo Long 16 bytes
OLE Object Long 16 bytes
Hyperlink Long 14 bytes


  The above is based on JET 3.5 (JET 4.0 has never been documented), but it will get you close enough.   There are a few wrinkles with OLE and Memo fields as well, but you can ignore that.

 So total all that up, then add as many text fields as you need to take the record size beyond 2048.  Don't go crazy and add a bunch as you want to be as close to 2048 as you can without going under.

  The reason is that text fields are stored variable length and the record may grow when edited because of that.  You don't see it too often, but you can get a "record too large" error when editing.

  That's also the reason why for the text fields that you add that you need to add a default value of something like "................................................" (with as many characters as you've set the field for).

  Otherwise the field takes up zero space and you don't get the padding.

Jim.
0
 
sxxguptaAuthor Commented:
ok have that.....my actual table will meet that for each record..........
0
 
sxxguptaAuthor Commented:
and needed help on how to use your function three threads up....
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<and needed help on how to use your function three threads up.... >>

  My apolgies...I lost track of this.

  The procedure can be called anytime you have a recordset with a record current.  For example, from a form you would call it like this:

    Dim rst As Recordset

    Set rst = Me.RecordsetClone
    intRet = IsLocked(rst, UserName, MachineName)
    Set rst = Nothing

   The first argument is expecting a recordset.

 Jim.  
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now