Re: [MS_AccessPros] record locking

Hendra-

I haven't tested a network shared back end. The "Locked" symbols appear on the Record Selector bar - a property you can set in the form.

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 5, 2014, at 6:44 AM, Agestha Hendra <agesthahendra@ymail.com> wrote:

> John,,,i have made Re-test as the same as you did(BE is on local machine),,and right, it worked as "Edited Record", the same result as your test...
> but do you think it will be diffrent if the BE in the network? i wanted to make a test again with diffrent Table on northwind, but at this time i'm having trouble with my network (Erorr on the router)....
> and where is the "Locked symbol" if the form is "Columnar"?....and thank you for the codes...
>
> 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, 3:26 PM
>
> 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 = 3270Public
> 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
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>



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

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/