Record locking

Is there a simple way to check if someone else on the network is editing the current record.

I run an on current routine that I do not want to run if the user moves to a record on a form that is already being edited by another user.
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Yes, you can try to edit the record and exit if that fails:

        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            ' Carry out your edits.
            rs.UpDate
        Else
            ' Exit.
        End If

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Set your record locking like this:
Lock record
0
Gustav BrockCIOCommented:
I should add that rs is the recordsetclone, and that you silently can ignore the error if Edit fails:

        Dim rs As DAO.Recordset
        Or Error Resume Next
        Set rs = Me.RecordsetClone
        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            ' Carry out your edits.
            rs.UpDate
        Else
            ' Exit.
        End If

/gustav
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Attempting to edit the record and trapping the resulting error is the only method.  The below code is very similar to gustav's comment, so no points please.

Just shows how to return the user that's locking the record.

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
0
PatHartmanCommented:
@boag2000
Setting Record Locks to Edited Record can create problems in a multi-user environment because as soon as the user begins typing in the form, the record is locked.  If he takes a phone call or leaves his desk, the record remains locked until he returns.

All records is obviously worse since all records could be locked for hours if your users are easily distracted.

No Locks is probably a misnomer since a lock is actually acquired but not until Access attempts to save the record.  So this is after the user has typed his data and pressed the "save" button, Access locks the record and then updates.  When Access saves the record it compares the timestamp field with the table record with the timestamp field of the record it is writing,  If they are the same it knows the record hasn't been changed and goes ahead and saves the record.  If they are different, it presents the user with a three choice message which most find quite confusing.
1 - overlay ---- ALWAYS instruct your users to NEVER choose this option because they are blindly replacing updates that they haven't seen.
2 / 3 - copy to clipboard or discard -- either of these options is safe.  It allows the user to see the change and decide if his edits need to be reapplied.  
If your tables don't use timestamp fields, Access compares all columns in the recordset to see if they have changed.  So if you selected fld1, fld2, fld3 out of 50, those three fields are compared to the versions in the table.  If they have changed since Access read them, you get the confusing warning.  This is just one more reason why your forms should be bound to queries and your queries should select ONLY the columns you actually need.  If you select 50 columns and you only needed 10, Access will have to compare 50 columns to determine if it can update a record.  And that runs more risk of finding a conflict since in our earlier example where you selected just three columns, a different user could have updated a column you didn't select while you were dawdling and you wouldn't get a write conflict since your update isn't conflicting with his.

If your forms display record selectors, Access gives you a visual clue that a record is being updated.   It shows a pencil when YOU have dirtied the record and it shows a circle with a slash if someone else is currently editing the record.

The solution posted earlier by cactus is a very simple way to warn your users if this is important to you.  But you really don't need to worry.  Jet/ACE and all the ODBC applications are quite capable of preventing update clashes so trapping the error message and displaying a better one for them is really the best solution.
0
Jeffrey CoachmanMIS LiasonCommented:
@PatHartman

Most top experts here are already familiar with what you posted...
;-)
But your post may provide a deeper insight into these settings for other members here searching for the same issue.
;-)

I simply suggested that setting as a way to lock the record, in case that is what they wanted.

JeffCoachman
0
Derek BrownMDAuthor Commented:
Thank you Pat that was indeed clear and helpful

I have managed to get Gustavs code to work but it is locking the entire record set when I only want to lock the current record in the subform.

Can I restrict the record set clone to just the subforms record.

Actually I'm not sure if this is causing the problem. But the whole form should not be locked, should it.

It's a main product form with items in a single items (not continuos) subform
0
Gustav BrockCIOCommented:
It is probably not locking the recordset but the current page which is a set of records of which the current record is one. A page holds that many records that fill 2 or 4K (can't recall).

Jim Dettman knows all about this.

/gustav
0
Derek BrownMDAuthor Commented:
The problem has now vanished. Weird. I am sure it will reappear but I cannot see where the discrepancy is. I think that I have covered the page locking as opposed to record locking by adding a number of text filled fields. (The Alphabet 10 times as a default in each field). This must fill up the 2 or 4K and that works.

Thank you all for your help
0
Gustav BrockCIOCommented:
Yes, not so elegant, but is known to work.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.