[AccessD] VBA Unbound data entry / update form

Gotcha!

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> I think you are misunderstand what I mean by Lock the Record.  In your code,
> check if the record's new field (call it what you want), "I am locked By
> UserName" is empty or not.  If it is empty, put an entry in it and then
> export the data to your working temp table.  If it is not empty, then
> somebody else is working on the record and the user gets advised of that
> fact.  The is no system lock involved.  Only the logical one that you
> created and which is handled by your code.  There are no decisions to make
> as regards what to write back.  Write the lot back.  You have the record
> safeguarded via your code (caveats re sideways access excepted).  Repeat -
> there are no system locks involved in this, therefore no other unrelated
> records being locked.
> 
> HTH
> Max
> Ps. It doesn't have to be a field that is used to indicate a "locked
> Record", you could use a global variable or write to a text file to say "PK
> is in use" and then delete that line when no longer required.  As long as
> your code checks that before allow edits then it should work.  However,
> there is a chance that two users may still collide when trying to do the
> "user locking routine", so you could use a temp table with a unique index on
> a field called PK.  This would prevent two records being created for the
> same PK as the main table and thus allocated to the first user to request
> locking and which will prevent new records being added until the previous
> record has been destroyed thus preventing duplicate simultaneous attempts -
> holds the PK of the record needing to be "locked".  Sorry, bit rushed that
> but I need to move on.  Hope it helps anyway.
> 
> 
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, May 28, 2008 12:17 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] VBA Unbound data entry / update form
> 
> Max,
> 
>  > Won't work John.
> 
>  >>Make the changes.  When done, look up the original record in the real
> table, compare data fields from temp to real. 
> If there are any changes THEN make the appropriate decisions etc.  This
> still requires a fair amount of logic to determine "old values" and the
> like...
> 
> Lock fields, lock notices, timeouts etc are a "fair amount of logic" as
> well.
> 
>  > 2. Lock it as soon as record is accessed/edited.
> 
> I can't do this.  The lock affects other entirely unrelated records due to
> memo and index "page locks".  THAT IS WHAT I AM TRYING TO WORK AROUND!
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Max Wanadoo wrote:
>> Won't work John.
>> If you have User 1 making changes, you need to ensure that User 2 is 
>> not making simultaneous changes to the same data albeit via a temp table.
>> Until you do that, nothing else makes any sense.
>> I would:
>> 1. Put a field on the table "I am locked By UserName".
>> 2. Lock it as soon as record is accessed/edited.
>> 3. Warn other users that records is being edited by UserName if they 
>> try to access it.  Ask them do they want to be notified when record 
>> becomes available.  If so, record PK and User in a temp table or in 
>> variable array and check it via an OnTimer event.
>> 4. You can also put a Time-Out default if UserName left a record 
>> Locked when he/she went off to luch.  Time-out and lock/leave the 
>> record thus unlockign it for others.
>> 5. KIS works for me.
>> Max
>> Ps. Also be aware of what you need to do if the record is locked by 
>> being reported on via a Report/update batch run, etc.  Remember record 
>> will be incomplete.
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>