rsEdit

I have this on a subform. When the form opens the form is not locked but the message "This record is being changed by another user." comes up every time even when there is no other user logged on. When I move to another record on the main form the error clears and all is well??

Have I got something in the wrong place?

Private Sub btnLock_Click()
Dim rs As DAO.Recordset
On Error Resume Next
Set rs = Me.RecordsetClone

If rs.EditMode = dbEditInProgress Then
    MsgBox "This record is being changed by another user."
Else
    rs.Edit
        Me.Form.AllowEdits = True
        Me.Form.AllowAdditions = False             '***************
       
End If
rs.UpDate
End Sub
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.

Jeffrey CoachmanMIS LiasonCommented:
I'm confused, doesn't that code notify the user if the record is locked...?
That code will run when the button is clicked..., so whats the code on the Open event?

What are you trying to accomplish with this system?

JeffCoachman
0
Derek BrownMDAuthor Commented:
Hi Jeff

I had a client that wanted to make sure that records in the subform could not accidentally be changed. So in the oncurrent event of the subform I have code that basically says when the record changes set the subform to allow edits false. Because of that I then had to place a button on the form to unlock so that the user could edit the record. But if another user already has the record open then the unlock button would not work and an error occurred. So the code is meant to say "if record is not locked by another user, unlock the record, if the record is already locked then show Msgbox".

Do I make stuff complicated or what?: Here is the full unlock btnLock code. You can see that it sets a hidden check box to -1 to lock. The LockUnlock code treats the btnLock button like an on off switch.

Private Sub btnLock_Click()
Dim rs As DAO.Recordset
On Error Resume Next
Set rs = Me.RecordsetClone

If rs.EditMode = dbEditInProgress Then
    MsgBox "This record is being changed by another user. Please wait until the other user exits the record before continuing"
Else
    rs.Edit
        Me.Form.AllowEdits = True
        Me.Form.AllowAdditions = False             '***************
    If FormLocked = -1 Then
        FormLocked = 0
    Else
        FormLocked = -1
    End If
        Me.Refresh
        LockUnLock
       
End If
rs.UpDate
End Sub

Public Sub LockUnLock()
If FormLocked = -1 Then
        Me.AllowEdits = False
        Me.Form.AllowDeletions = False
        Me.Form.AllowEdits = False
        btnLock.ForeColor = vbGreen
        btnLock.Caption = "Edit"
Else
        Me.AllowEdits = True
        Me.Form.AllowDeletions = True
        Me.Form.AllowEdits = True
        btnLock.ForeColor = vbRed
        btnLock.Caption = "Lock"

End If
        Me.Form.Refresh
End Sub
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your mis-understanding what Edit mode is.  Doesn't tell you if another user is in there, it only tells you if the current record is currently in the copy buffer.

 The only way to tell if another user is in is to set pessimistic locking (Edited records) and then make an attempt to edit the record.  If that fails with an error, then another user has the record locked.

You also might want to take a look at this:

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

as an alternative.

Jim.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
> Have I got something in the wrong place?

Yes, you need to call Edit first, then read the outcome, EditMode.
However, having called Edit, at the same time you have refreshed the record so it now is up to date. This means that you usually just need to call Edit once more to enter edit mode.

The function below does this, and you just need to call that in place of Edit like this:

Set rs = Me.RecordsetClone
' Refresh record and leave it in edit mode.
Call SetEdit(rs)
' Leave edit mode.
rs.UpDate
' Allow user to edit.
Me.Form.AllowEdits = True
Me.Form.AllowAdditions = False    

Public Sub SetEdit(ByRef rs As DAO.Recordset)

    On Error GoTo Err_SetEdit

    Do While rs.EditMode <> dbEditInProgress
        rs.Edit
        If rs.EditMode = dbEditInProgress Then
            ' rs is ready for edit.
            Exit Do
        End If
    Loop

Exit_SetEdit:
    Exit Sub

Err_SetEdit:
    Debug.Print "Edit", Timer, Err.Number, Err.Description
    ' Continue in the loop.
    ' Will normally happen ONCE only for each call of SetEdit.
    Resume Next

End Sub

Open in new window

/gustav
0
Derek BrownMDAuthor Commented:
I was away all day yesterday so could not post a comment. My apologies.

That looks brilliant, I wish I understood it.

I put SetEdit where I thought it would be called, in the btnLock procedure, and got error message "Augment not optional"

Where do I put it, how do I call it and do I need to put anything in the area "rs. isready for edit"

Thanks Gustav
0
Gustav BrockCIOCommented:
As shown above ...

/gustav
0
Derek BrownMDAuthor Commented:
Ooops! Should pay attention. Apologies again.

With Jim's comment above, with your code are we looking for another user trying to edit the same record or just trying to see if the record is being edited ?

What I am trying to do is to stop performing any automatic updates when another user is editing a record from a remote computer.
0
Gustav BrockCIOCommented:
We are just testing if another user has changed the data since we read the record and, if so, the record is refreshed.
We can then start editing. However, another user may start editing shortly after. If we update first, the other user user will see a message and - vice versa - if the other user updates first.

This is not an error. You just have to consider the possible scenarios and plan what to do for either of these.

/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
Derek BrownMDAuthor Commented:
Jim is usually right on this stuff, but I see no flaw in your logic

Thank you for your help.....................and patients

Derek
0
Gustav BrockCIOCommented:
You are welcome!

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