John W. Colby
jwcolby at colbyconsulting.com
Tue Aug 3 20:44:12 CDT 2004
Agreed. I have an active and a trash flag (Booleans). Active means just that, active in the database, should be showing at all times. Inactive means not ready for archive but still an active record. Trash means someone tried to do a delete. The state table looks like: Active=True, Trash=False - Record is active Active=False, Trash = false - Record is inactive Active=False, Trash=True - Someone tried to delete Active=True, Trash=True - Invalid state. I have relationships set between virtually every parent / child with cascade delete turned off. This causes any attempt to delete where a child record exists to generate a jet error. My error handler simply asks "are you sure you want to delete", and if the answer is yes, clears the active flag and sets the trash flag, requerying the form. All forms display only Active records. Since the active flag was just cleared, the record "disappears" or appears to be deleted. You would not believe the relief on the user's face when you undelete a record they know they shouldn't have deleted. Worth more good will than anything else you can do for them. I am able to prevent deletes on records with no children but in many cases I don't bother. Many times a single record with no children should be allowed to be deleted anyway. Mistaken data entry, trash records etc. Since I use a framework, and this stuff is built into the form class, it is just there, available for any form that uses the form class. John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK Sent: Tuesday, August 03, 2004 8:43 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Coding for backing up deletes Hi Paul I rarely let my users 'delete' records In virtually all my tables I have a field called IsDeleted - Data Type = Number All my 'delete' buttons actually set the value for IsDeleted in that record to -1 Then the form refreshes to NOT show records where IsDeleted =-1 That way we can give the appearance (and make yourself look good in the process) of having the ability to undo or rollback when users 'stuff something up' after they have left the record or closed the form I actually then move (at whatever period suits) all records where IsDeleted = -1 to another table or dB I rationalise this to all my users (the ones involved at admin level in the design specs at least) and I haven't had anyone disagree (yet). In fact when you explain the benefits and low overheads al my customers (so far) agree it's a great idea. You can then build them an interface (I do) to recover 'deleted' records or data. I don't let 'em edit data through those interfaces - just recover 'deleted' records or REALLY delete - depending on Logon Permissions. It's all pretty transparent to the users and the Admin people really know what's going on and love the ability of a 2nd chance recovery or proper deletion Hope this helps Darren