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

 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

Select allOpen in new window

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

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?

"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

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

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?

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

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.

 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

https://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.

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.

JD:
How do I "pad" a record?

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

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

Select allOpen in new window

<<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.

ok have that.....my actual table will meet that for each record..........

and needed help on how to use your function three threads up....