With the database system itself, you'd need to use pessimistic locking (record gets locked as soon as a user starts to edit a record), which is "Edit locking" in Access and make the record dirty when they click the button.

The problem is that often you cannot get it to lock down to the record level (you end up locking a page of records) and with Access specifically, it's tough to control.

So many build their own locking scheme independent of what the RDBMS does leaving that set at optimistic (only locks when the actual update to the database is made). This is "No Locks" in a Access form.

Doing locking on your own, you can have a lot more flexability (ie. Locking out a module, such as all of AR) with being able to lock more then just tables or records. Any type of resource can be locked (printers, a specific function, use of a specific form, etc)>

The drawback of course is that your really not locking anything this way. If part of your app doesn't bother checking for contention, then there's nothing stopping it from going ahead and using the resource.

For what it is worth, I use a technique similar to Jim's "A Better Approach" section in several of my applications.

I use the current event of the form to test whether the current record is already locked, and if so, disable the edit button and display a label that indicates the record is locked, and who it is locked by.

I also use the Form_Current and the Form_Close events (or my Save or Cancel buttons) to remove locks for the current user, table combination, so that all previous locks for this combination of user/table get removed when I close the form or move to a new record.

When the user clicks the Edit button, I write a record to my ResourceLocks table.

I'll also add that opening an editable form to the first record isn't a good practice. That poor "alphabetically-challenged" record will get many mistaken edits, may cause locking issues, and can hurt performance too (since Access is opening a large updatable recordset and managing more locks.)

Instead, the way we approach this is to create a read-only continuous form that allows the user to sort, select and search records. When they drill down, they see one editable record.

Fyed. Can you let me have an example of how you, "use the current event of the form to test whether the current record is already locked, and if so, disable the edit button and display a label that indicates the record is locked, and who it is locked by."

Armen. I try to avoid forcing my clients to start their program having to find a record. To my mind when I open an form it should open where I left off. I have arranged to have the form, not the record, locked so it takes a deliberate action to start editing the first record. I hate forcing my customers to "Drill down" when they clearly want to continue where they left off. However in a multi user environment this may cause me problems. So all I want to do is say "Lock the form only if not being edited by other user."

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Featured Post

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…

With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks.
Specify a start-up form through options:
Specify an Autoexec macro:
Us…