How to create a message box for edited record locks? (1 Viewer)

[omarrr128]

I have set the Form Record lock type to "Edited Records" only. When a user tries to edit a record which is locked by another user they will hear a Windows error ding sound but I want a message box to pop up too. I have tried searching it up and even asking Chat GPT to create some code but nothing seems to work. I feel like there has to be something to make a message box come up for this.

Anyone know how?

Thank you

 

[arnelgp]

see if this link will give you what you need:
Recipe 10.9 Determine if a Record Is Locked and by Whom :: Chapter 10. Multiuser Applications :: Access :: Microsoft Products :: eTutorials.org

 

[arnelgp]

Chat GPT to create some code but nothing seems to work.

if it does, then we are doomed. T-800 will come and terminate us all.

 

[omarrr128]

if it does, then we are doomed. T-800 will come and terminate us all.

honestly it gave some very good and different suggestions and one that almost worked.

 

[omarrr128]

see if this link will give you what you need:
Recipe 10.9 Determine if a Record Is Locked and by Whom :: Chapter 10. Multiuser Applications :: Access :: Microsoft Products :: eTutorials.org

site doesnt seem to work for me unfortunately, even on my phone

 

[ebs17]

A very lean approach:

Private Sub Form_Current()
    If IsRecordLocked Then MsgBox "Record locked"
End Sub

Private Function IsRecordLocked() As Boolean
    On Error Resume Next
    With Me.Recordset
        .Edit
        .Fields("ValueX") = .Fields("ValueX")
        .Update
    End With
    If Err.Number > 0 Then IsRecordLocked = True
    On Error GoTo 0
End Function

ValueX here is a simple field in the record, not exactly a key.
With the attempt to overwrite the field with the own content, it is tested for errors.

 

[arnelgp]

ValueX here is a simple field in the record, not exactly a key.

possible...
but will always dirty the form.
not advisable when the back end is not access and much traffic is going on.

 

[Pat Hartman]

For most forms, optimistic locking is the preferable setting, because it keeps records locked for a much shorter period of time.

You need to think long and hard before you try to get into the locking management business. NO WAY can you handle this better than Jet and ACE. Plus, if your table is some other RDBMS, you cannot use Access lock settings to manage locking anyway.

With optimistic locking (the default) each record is locked only long enough to effect the change so it dramatically reduces the potential for contention even in a busy application given the blink of time the update actually takes. With pessimistic locking (Edited Records - what you are using), the record is locked as soon as it is dirtied and it STAYS LOCKED UNTIL the user saves it or uses esc to back out of the update. So, if the user is interrupted by a phone call or the siren call of lunch, the lock could persist for hours and could even block a backup if left overnight. At best, the lock will persist for multiple seconds at least and probably minutes rather than a microsecond.

If your form shows the RecordSelecter, there are three icons:
1. Right triangle - current record (only relevant on a continuous or ds view form)
2. Pencil - record is dirty (ie being edited on THIS computer)
3. Circle with line - record is being updated by a different user.

When a user sees the Circle with line, he should NOT modify the record at this time. But even if he doesn't, he could still get an error message. Assuming the first user committed his changes, the underlying data on the server has now been changed and therefore, the values the second user is seeing might have been changed by the first user. Therefore, if he now makes a change and tries to save, he will still get an error because there is a conflict. The message is confusing and gives three options. You need to train your user to simply back out of the edit and try it again. This will retrieve the current column values which might be different than they were a few moments ago.

The most confusing situation is where UserB is is faster than UserA and saves first. UserA never saw the locked icon and so is surprised by the error when trying to save. Anyway, there's a limited number of situations and you can create screen shots and displays of the messages along with instructions and it will be safer and easier than trying to manage record locks.