Max Wanadoo
max.wanadoo at gmail.com
Wed May 28 08:00:39 CDT 2008
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