VBA Record Locking

Linder1976
Linder1976 used Ask the Experts™
on
I have a form when it loads I bind the records to the fields like so:

me.recordsource = "sql statment....

Is there a way to lock that record when it loads, and not when data is change. I want to be able to lock the form so if another person tries to open the same record, they can view it but will be notified they cannot edit it. Is that possible? Whether the first person is eidting the data or not, if the data is open I need that record locked.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Access has no native ability to do that, but you could add a Boolean column to the table and set it when the user opens the record. When other users attempt to open a record, you could then query for that field and see if it's set to True. If so, alert the user.

The drawback to a setup like this is you can end up with "hanging chads" - that is, if for some reason the user's workstation abruptly terminates a connection, a record could be marked as "in use" when it's not really in use. You'll need some mechanism to handle things like this, although you may find those times few and far between.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Essentially, there is no practical way to do this w/o issues.

mx

Author

Commented:
So I have that option and maybe use in conjuction with pessimistic locking where when it isaves that record is locked until the save is done.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Record locking is very confusing in Access ... and has changed somewhat over the years.  I would suggest you read these KB's before making changes:


http://support.microsoft.com/kb/275561/EN-US/  'New features that are included in Microsoft Jet 4.0

http://support.microsoft.com/kb/306435/  ' Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60

http://msdn.microsoft.com/en-us/library/aa165294%28office.10%29.aspx 'Handling Locking Conflicts

http://msdn.microsoft.com/en-us/library/aa189633(office.10).aspx  'Page-Level Locking vs. Record-Level Locking

mx
Eric ShermanAccountant/Developer

Commented:
Why not shift the focus to lock the record when the user makes a modification to it rather than when the table is open??

ET

Author

Commented:
So for example, you are saying, if a person opens a form, and goes to record 1, that record is not locked until let's say the user changes the data in a field? Is that correct?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
", that record is not locked until let's say the user changes the data in a field? Is that correct?"

At a minimum ...

mx

Author

Commented:
When does the record actually lock so another user who brings up the same record can't edit it.
Eric ShermanAccountant/Developer

Commented:
>>>>So for example, you are saying, if a person opens a form, and goes to record 1, that record is not locked until let's say the user changes the data in a field? Is that correct?<<<<<

Depends how you have Record Locking set under Tools >>> Options and Record Locking in the Form properties.  I generally set those to No Locks.  If two users access the same record for editing the Save action will prompt a message to the second user.  

ET
Database Architect / Application Developer
Top Expert 2007
Commented:
In theory, when you edit a bound field.  However, a 'page' of records is likely to be locked, not just that one record.  Seriously ... read the KB's I posted ... not that long ... to get the background on this, instead of in bits and pieces.

mx
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Note that there is a work a round to get record level locking if you need it, which is to pad records so that they take up more then half a page.
JimD.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial