How to check if record is Locked

[Wayne]

I have a multi user database where most users access one form to input data.
Users open an existing customer record in the database, the record becomes
bound to the form, the user updates the necessary details in the existing
record and closes. This generally works well, as each customer has their own
manager so generally two staff don't try to access the same record
simultaneously.

The problem is the supervisors! Somehow they always manage to open the same
records as the users. The record is bound to the form by VBA, and record
locks are set to "Edited Record" on the form. Even though there are only two
supervisors, they generally manage to lock people out of their own records.

Is there any way to test when a record is opened whether it is locked for
editing by other user? I do want record locking to apply for this form as I
don't want two users to update a record simultaneously. I added specific code
in my error trapping to provide a custom warning message about updating a
locked record, but i'd rather they get this warning when they Open the record
(and lock all the controls on the form), rather than when they try to save.

 

[Allen Browne]

Unfortunately, Access does not expose any property that indicates the locked
state of the current record in the form.

While that would be very handy, there are actually many factors MS would
need to handle to give that to us, such as:
- the locking strategy (optimistic/pessimestic),
- page size (varies with versions and Unicode settings),
- whether each user's front end is set to Record- or Page-level locking,
- the version of the back end (e.g. thunking of JET 3.5 calls to JET 4, or
even a non-JET back end),
- the network traffic and communication strategies for flagging each time a
record is dirtied, undone, or saved,
- handling coded updates as well (recordsets with their buffers, executing
action queries),
- handling transactions,
- handling cascading relations, etc.

So, some general workarounds:
1. Use optimistic locking if possible. This is the most tested scenario, and
suitable for the vast majority of applications. There is no sense in you, as
the developer, taking on the responsibility for this. Just train your users
to handle the write-conflict dialog.

2. Do not dirty the record unnecessarily. It's quite common to see silly
things like code in Form_Current that dirties the record as soon as the user
arrives there, or Form_AfterUpdate that dirtying it again as soon as the
record is saved.

3. If it is important to avoid accidental dirtying, consider loading the
form so the controls are locked and the user has to click a button to enable
edits. There's an example of how to do that here:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html