ACCESS FEED: RE: [MS_AccessPros] record locking

I have them here and can upload them if you like.

Glenn

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, March 4, 2014 11:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] record locking

Bill-

They came across fine in email. I didn't keep the screen shots, so can't build a Word doc without rebuilding the example from scratch.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 4, 2014, at 4:12 PM, <wrmosca@comcast.net> <wrmosca@comcast.net> wrote:

John

All I'm seeing are image placeholders in your post. Can you upload a Word doc with the images?

-Bill

---In MS_Access_Professionals@yahoogroups.com, <JohnV@msn.com> wrote :

Hendra-

I went into Northwind 2007 and modified the Customer Details form to Edited Record and added a Record Selection Bar so we can see what is going on. I then opened a second copy of the database in Access on the same machine, and started to edit the first record. This is what I saw:

While leaving that edit pending, I then went to the second copy and tried to edit the same record. Access wouldn’t let me:

Notice the “locked” symbol on the Record Selection bar.

I went to the second record in the second copy and started an edit:

Works just as expected.

As for error trap code, here’s an example:

In a Standard module:

' Common Form_Error codes
Public Const errCancel As Long = 2501
Public Const errCancel2 As Long = 2001
Public Const errDuplicate As Long = 3022
Public Const errInvalid As Long = 2113
Public Const errValidation As Long = 2116
Public Const errInputMask As Long = 2279
Public Const errRI As Long = 3200
Public Const errCustomValidate As Long = 3316
Public Const errTableValidate As Long = 3317
Public Const errSearchEnd As Long = 8504
Public Const errSpellCheck As Long = 9536
Public Const errGeneral As Long = 3316
Public Const errPropNotFound As Long = 3270

Public Const errRecordChanged As Long = 7787

In the form’s Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
' This is standard code that I put in the Error event
' of all forms that edit data. The idea is to try to intercept
' most of the common standard error messages - most of which are
' not user-friendly - and deal with them here.

' Pick options based on the error code
Select Case DataErr
Case errCancel, errCancel2, errPropNotFound ' Cancel - ignore
Response = acDataErrContinue
Case errDuplicate ' Duplicate row - custom error message
MsgBox "You're trying to add a record that already exists. " & _
"Enter a new Department ID or click Cancel.", vbCritical, gstrAppTitle
Response = acDataErrContinue
Case errInvalid, errInputMask
' Invalid data - custom error and log
MsgBox "You have entered an invalid value. ", vbCritical, gstrAppTitle
ErrorLog Me.Name & "_Error", DataErr, AccessError(DataErr)
Response = acDataErrContinue
' Field validation, Table validation, Custom Validation, End of Search, Spelling Check
Case errValidation, errTableValidate, errCustomValidate, errSearchEnd, errSpellCheck
' Do nothing -- let the standard message display
' All validation rules in the tables have custom error messages.
Response = acDataErrDisplay

' Record updated by someone else

Case errRecordChanged

' Issue special instruction message

MsgBox "Someone else edited and saved this record while " & _

"you were making changes." & vbCrLf & vbCrLf & _

"Select Drop Changes in the following dialog and reenter your changes."

' Also display standard message

Response = acDataErrDisplay
Case Else
' Dunno - log and let error display
ErrorLog Me.Name & "_Error", DataErr, AccessError(DataErr)
Response = acDataErrDisplay
End Select
End Sub

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 4, 2014, at 4:06 AM, Agestha Hendra <agesthahendra@ymail.com> wrote:

Thank you John,...

First, would you give me the "error trapping in the Form_Error event" example like you had said...
Regarding the test,...yes i had setted the from --- tool --- options --- Advanced : and “Open Databases by Using Record-Level Locking” is checked...
But maybe someone here would do the same test as i did, because i still don't really sure about my conclusion, maybe i missing something to set..

Regards
hendra Agestha
--------------------------------------------
Pada Sel, 4/3/14, John Viescas <JohnV@msn.com> menulis:

Judul: Re: [MS_AccessPros] record locking
Kepada: MS_Access_Professionals@yahoogroups.com
Tanggal: Selasa, 4 Maret, 2014, 12:53 AM

Hendra-

First, I never use “Edited Record” - I prefer No
Locks. The downside is if two users edit the same
record at the same time (A starts to edit, then B starts to
edit, then A or B save before the other is done), one user
will get an error message stating that the record was
changed by another user. You can trap this error in
the Form_Error event and issue your own message.

Technically, your test should have worked, however (as long
as you were trying to edit three different records).
Are you sure you have the “Open Databases by Using
Record-Level Locking” checked in Access Options / Advanced
/ Advanced?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 3, 2014, at 6:45 PM, Agestha Hendra <agesthahendra@ymail.com>
wrote:

I'm following this thread...so i decided to made a test
for the "locking level" ability, i placed a northwind.mdb as
BE on a computer on a network, then i opened 3 FE on my
laptop (i copied the FE 3 times and then opened them all), i
opened "Products" form for all FE, but i had setted the
form's property to "edited record" (all of 3 FE).

For the 1'st FE i tried to edit the 1'st record with
succesfully...but i let the 1'st form stay opened, then i
opened the same form (on 2'nd FE) and tried to edit 2'nd
record...it didn't work,,,then i opened the 3'rd FE tried to
edit the 3'rd record...it failed too...

From that experiment i make a conclusion that ms access
"locking level" not behave properly like it should, it seem
that all the page/table is locked even we had setted the
form property to "edited record"...Sorry if my words
confusing you since implementing English "time progress"
grammar is difficult as writing 100 lines vba codes... :D

Regards
Hendra Agestha
--------------------------------------------
Pada Jum, 20/9/13, wrmosca@comcast.net
<wrmosca@comcast.net>
menulis:

Judul: RE: RE: [MS_AccessPros] record locking
Kepada: MS_Access_Professionals@yahoogroups.com
Tanggal: Jumat, 20 September, 2013, 9:45 PM



Liz
The
default record locking is for new forms. Unlike John, I
use

Edited Record most of the time, but I'll keep his
suggestion in mind should I run up against locked
records.
Bill

--- In MS_Access_Professionals@yahoogroups.com,
<ms_access_professionals@yahoogroups.com>
wrote:

Thanks John, and from such an
expert! I am so fortunate to have you in my
life.

Hey, so when I look at the “options
/ advanced” section of the database I see 2
different

switches… radio buttons for default record
locking:

one that has the default record locking (set at edited
record) and
another checkbox that says open databases by
using record

level locking. I don’t understand this
distinction.


Respectfully,
Liz
Ravenwood
Programmer /
Analyst
B/E
Aerospace
|
Super
First Class Environments

1851 S Pantano
Road
|
Tucson,
Arizona 85710
Office
+1.520.239.4808
|

beaerospace.com
Passion to Innovate.
Power to Deliver



From:
MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com]
On Behalf Of John Viescas

Sent: Wednesday, September 18, 2013 10:38 PM

To: MS_Access_Professionals@yahoogroups.com

Subject: Re: [MS_AccessPros] record
locking



Liz-



Default
locking for forms should always be No Locks, especially
in a

multi-user app. Default locking for a Recordset
in

code is always the equivalent of Edited Record (which
is

good) unless you specify something different when you
execute
the OpenRecordset. Edited Record in a form
locks the

record (and perhaps the entire page on which the
record

resides) as soon as the user "dirties" the record.
If a user starts to edit a record and then goes
off

for a potty break of another cup of coffee,
that can potentially lock other users out of a
bunch of

records for a long time. With No Locks, nobody
gets

locked out. You will have a locking problem only
if

two users try to edit the same record at the same
time.

When that happens, Access notifies the
second user who tries to save that someone else
has updated

the record. The second user's changes get thrown
away, but the user can simply re-enter the lost
change.



HTH...



John
Viescas

Sent from my iPad

On Sep 19, 2013, at 0:42,
"Liz Ravenwood" <liz_ravenwood@beaerospace.com>
wrote:

Pros,
what’s the difference between default record locking
at

edited record and open record with record locking or
whatever it is?
I
occasionally get an error on a lock and was googling
and

someone quoted Allen Browne as saying untick some
record

locking business of sorts.


Respectfully,
Liz
Ravenwood
Programmer /
Analyst
B/E
Aerospace
|
Super
First Class Environments

1851 S Pantano
Road
|
Tucson,
Arizona 85710
Office
+1.520.239.4808
|

beaerospace.com
Passion to Innovate.
Power to Deliver


This email (and all
attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or
proprietary

information. Any unauthorized review, use, disclosure
or distribution is prohibited. If you are not the
intended

recipient, please contact the sender by reply e-mail
and

destroy all copies of the original message.

This email (and all attachments) is for the sole
use of the intended recipient(s) and may contain
privileged

and/or proprietary information. Any unauthorized
review,

use, disclosure or distribution is
prohibited. If you are not the intended
recipient, please

contact the sender by reply e-mail and destroy all
copies of

the original message.

------------------------------------

Yahoo Groups Links

------------------------------------

Yahoo Groups Links

MS_Access_Professionals-fullfeatured@yahoogroups.com

------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/