harmankardon, are you still dealing with this issue? I was just reading through this thread. I'm doing something similar to what has been suggested. I've used a separate table (I call it ttTransactions) to store the record ID of the record a user wants to edit. If another user tries opening any form in the front-end that has edit capabilities, the form first checks to make sure the ID isn't in that table. If it is, a message displays and lets them know a transaction is currently in process for that record and then closes the form. When the person who was making the edits is done and the form closes, the record is deleted from the ttTransactions table and anyone else is free to make edits. I ran into the exact scenario you described, with an inventory control system I created. Multiple people are in the system at a time and the potential is high that two people could try editing a record at the same time.
I'm doing something similar to this solution to create an auto number for packages in that system. I didn't want to use the autonumber feature because if a user cancels an entry, that number is gone forever. My forms are also unbound. When a user clicks on the form to create a new package, the code finds the highest number already assigned, adds 1, and then temporarily puts that number in a table called ttCSNumbers. There are multiple workstations from which users can check in new packages. If someone else opens the form to check in a new package, the form checks both the table where the records are ultimately stored AND the ttCSNumbers table and takes the next one. Once the form is closed, the number is deleted from the ttCSNumbers field. If I didn't do this, two packages could be checked into the system with the same number. Sure, I could simply enforce unique values in the SQL table, but by the time the user fills out all the information and two people enter their initials and password they've done quite a bit of work. I would make a lot of users unhappy if they were constantly being told they just wasted 5 minutes because someone else had the form open.
Maybe there are better ways to do this programmatically, but this works for me. I've even done this same thing in an application that uses SharePoint 2010 lists as tables in an Access front-end. From that perspective, my solution can be used no matter what system you use as a back-end.