How to create a message box indicating record is locked?

[kelsita_05]

I have done some serious searching and have discovered that many others have posted this question, but I couldn't find any satisfactory answers.

I have a FE/BE Access 2000 database in a multi-user environment. I have my default record locking set to "No Locks" (optimistic), but for unknown reasons, some records still lock. The records are viewed via a form, which is also set to "No Locks." We only have about 20 users, so it is rare that two people actually have the same record open at the same time. It seems to be that whole groups of records lock sometimes, despite the fact that I'm using optimistic record locking. Usually if you wait 15-20 minutes it is unlocked. If someone can explain this I would be overjoyed.

But, I can deal with the record locking. The problem is that users have no idea why they can't update a record and no matter how many times I tell them to wait 15 minutes they still come crying to me every time. So, I want to make a message box pop up when they try to update the record that states something like this: "The record is currently locked. Please try again in 15-20 minutes."

If someone could offer some code options and explain where it needs to go (form load, open, etc.) I would be eternally grateful.

Thank you!

 

[Durien512]

i am really new at all this stuff but this might work

is it an error message that they get?

if so you can do a on error event on the form

Private Sub Form_Error(DataErr As Integer, Response As Integer)
msgbox "The record is currently locked. Please try again in 15-20 minutes."

End Sub

i use a similar code that if they forget to enter something it throws an error since its a required field then, a message box pops up and tells them

 

[KeithG]

Does the message produce an error number?

 

[kelsita_05]

The problem is they don't get any error. It just makes a lot of little "dings" when they try to type anything. Although, I suppose I could set it up to save the record when they enter a field, which I think would make an error message pop up. Hmmm... I'll try it. No thoughts on why the records are locking in the first place?

Thanks

 

[kelsita_05]

So, in theory, I could set it up to save on enter in the field they will most likely be trying to update. That would create an error message when they can't enter data, yes? The problem is that it is very hard to recreate the record locking since it happens on a random basis for unknown reasons, so it would be very hard for me to capture the error to find out the error number. I can't do the on error event for the whole form because I have other messages set up for errors in specific fields. Based on my list of error numbers, there are about 6 that could potentially result when a record is locked.

oh my

 

[gold007eye]

I had the similar problem, but mine is with 2 databases linked to the same tables. So what I did was have a custom error message when opening the form if it was stating that the record was locked by another user:

On Error GoTo Err_Open_Click

Exit_Open_Click:
    Exit Sub

Err_Open_Click:
    Beep
    MsgBox "You are currently trying to open the " & [Combo85].Value & " form.  This form is currently LOCKED by another user.  Please try again in a few minutes.", vbInformation, "Form " & [Combo85].Value & " Currently Locked by Another User..."

Resume Exit_Open_Click

 

[Sean O'Halloran]

You may be trying to work around a problem that can be fixed cleanly, rather than telling your users to wait. If I may suggest, read this thread "Trouble opening database for multiple users."

Best of luck, and I'd like to know how you resolved / resolve this....

Smilin' Sean