UI Question: Protecting Critical Delete?

[(PeteCresswell)]

I've got apps where you *really* wouldn't want to delete certain items by
accident, but the users just have to have a "Delete" button.

My current strategies:

Plan A:
------------------------------------------------------------------------
1) Make the cmd button black and do not give it an accelerator key.

2) Issue two levels of confirmation (Do you want to delete... Do you REALLY want
to delete).

3) Log the delete in the .INI file
------------------------------------------------------------------------

PlanB:
------------------------------------------------------------------------
1) Do not assign an accelerator key to the cmd button.
2) Issue one confirmation
3) Instead of just deleting, move all Parent/Child records affected
to an "archive" DB - same format as production, just empty.
------------------------------------------------------------------------

I think I've done Plan B once or twice. It's not as simple as it may sound at
first because of things like autonumber fields.

Plan A is my usual approach, but I'm not exactly in love with it.

Anybody got a more creative approach to this?
--
PeteCresswell

[David W. Fenton]

"(PeteCresswell )" <[e-mail] te in
news:t7******** *************** *********@4ax.c om:

I've got apps where you *really* wouldn't want to delete certain
items by accident, but the users just have to have a "Delete"
button.

My current strategies:

Plan A:
-------------------------------------------------------------------
----- 1) Make the cmd button black and do not give it an
accelerator key.

2) Issue two levels of confirmation (Do you want to delete... Do
you REALLY want to delete).

3) Log the delete in the .INI file
-------------------------------------------------------------------
-----

PlanB:
-------------------------------------------------------------------
----- 1) Do not assign an accelerator key to the cmd button.
2) Issue one confirmation
3) Instead of just deleting, move all Parent/Child records
affected
to an "archive" DB - same format as production, just empty.
-------------------------------------------------------------------
-----

I think I've done Plan B once or twice. It's not as simple as it
may sound at first because of things like autonumber fields.

Plan A is my usual approach, but I'm not exactly in love with it.

Anybody got a more creative approach to this?

Don't delete anything. Just have a DELETED flag in the record, and
have the delete button mark it deleted. Then have your
forms/reports/etc. exclude all records marked deleted.

Restoring a deletion then requires nothing more than clearing the
Deleted flag field. And you won't need to bother with archive tables
and the like for the parent/child tables.

Certain kinds of data should never be deleted. For instance, a
customer with invoices can't ever be deleted as that would mess up
the accounting records. You might want to hide inactive customers,
though, such as ones who've not purchased anything in the last year
or two.

The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it. If not all the users are qualified to make that
decision, then only give the delete permission to certain qualified
users. And a user who is not allowed to delete should have no delete
button visible (that means hiding it instead of just disabling it),
on the principle that you don't want to suggest a user can do
something they are not allowed to do.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[(PeteCresswell)]

Per David W. Fenton:

>The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.

Me too - but Plan A's multiple prompts were first thought of by one of my users
who just *had* to have it exactly that way.... So I stuck with it....

I've tried the IsDeleted thing in one application.

Might do it again... might not. What I found is that the added complexity
propagates through the app to a greater degree than one might think.

e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also some
users doing ad-hoc reporting will not know about it and their reports will be
skewed accordingly.

Things like that and having to make sure every single query looks at IsDeleted
put me off of it - and steered me to the archive strategy.

You get 600-700 reports in an app and sooner or later, I'm going to forget about
IsDeleted and the results are going to pass testing because the situation wasn't
encountered and the reports going to be wrong sometime in the future.

Having said all that, you might be right. Certainly I haven't been able to come
up with anything better. The current app is pretty straightforward and I could
probably confine use of an IsDeleted switch to a single table.
--
PeteCresswell

[tina]

i'd have to agree with David; even when users tell me that data can be
deleted, during process analysis, i rarely take them at their word and build
in a "cushion" of time where deleted records...aren' t. solving the reports
problem is simple enough - never bind a report to a table. create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.

hth
"(PeteCresswell )" <[e-mail] te in message
news:gm******** *************** *********@4ax.c om...

Per David W. Fenton:

The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.

Me too - but Plan A's multiple prompts were first thought of by one of my

users

who just *had* to have it exactly that way.... So I stuck with it....

I've tried the IsDeleted thing in one application.

Might do it again... might not. What I found is that the added

complexity

propagates through the app to a greater degree than one might think.

e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also

some

users doing ad-hoc reporting will not know about it and their reports will

be

skewed accordingly.

Things like that and having to make sure every single query looks at

IsDeleted

put me off of it - and steered me to the archive strategy.

You get 600-700 reports in an app and sooner or later, I'm going to forget

about

IsDeleted and the results are going to pass testing because the situation

wasn't

encountered and the reports going to be wrong sometime in the future.

Having said all that, you might be right. Certainly I haven't been able

to come

up with anything better. The current app is pretty straightforward and I

could

probably confine use of an IsDeleted switch to a single table.
--
PeteCresswell

[Bri]

I also use this method, but rarely need to have the only not-deleted
query on anything other than parent tables as the child records can't
get selected if the parent isn't. You can go one step further and use a
Deleted Date field to purge things after they have been flagged as
deleted for long enough that they really should be gone.

--
Bri

tina wrote:

i'd have to agree with David; even when users tell me that data can be
deleted, during process analysis, i rarely take them at their word and build
in a "cushion" of time where deleted records...aren' t. solving the reports
problem is simple enough - never bind a report to a table. create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.

hth
"(PeteCresswell )" <[e-mail] te in message
news:gm******** *************** *********@4ax.c om...

>>Per David W. Fenton:

>>>The things you describe in the UI sound horrid to me. If they should
be allowed to do it, then let them do it and don't make them feel
bad about it.

Me too - but Plan A's multiple prompts were first thought of by one of my

users

>>who just *had* to have it exactly that way.... So I stuck with it....

I've tried the IsDeleted thing in one application.

Might do it again... might not. What I found is that the added

complexity

>>propagates through the app to a greater degree than one might think.

e.g. Referential Integrity raises it's head when the user goes to delete a
lookup table item that they *know* isn't being used by anybody. Also

some

>>users doing ad-hoc reporting will not know about it and their reports will

be

>>skewed accordingly.

Things like that and having to make sure every single query looks at

IsDeleted

>>put me off of it - and steered me to the archive strategy.

You get 600-700 reports in an app and sooner or later, I'm going to forget

about

>>IsDeleted and the results are going to pass testing because the situation

wasn't

>>encountered and the reports going to be wrong sometime in the future.

Having said all that, you might be right. Certainly I haven't been able

to come

>>up with anything better. The current app is pretty straightforward and I

could

>>probably confine use of an IsDeleted switch to a single table.
--
PeteCresswe ll

[Tim Marshall]

David W. Fenton wrote:

>>Anybody got a more creative approach to this?

Don't delete anything. Just have a DELETED flag in the record, and
have the delete button mark it deleted. Then have your
forms/reports/etc. exclude all records marked deleted.

That's what I was going to suggest, sort of based on the way dBase used
to do things when I was developing in dBase III+ and IV in the late
80s/early 90s. Of course, you could still .pack with that stuff....
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[(PeteCresswell)]

Per tina:

create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.

I think you guys are winning me over.

The base query idea was the tipping point. I could use a Find-And-Replace
utility to just change all affected table names to the base query name.

Main remaining problems that I can think of are:
------------------------------------------------------------------------------
- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.

- Some sort of pseudo RI issue where a user "Deletes" a lookup table row that is
used by one or more non-deleted other table rows - causing various queries to
return Null for the looked-up value.

My kneejerk is to just not implement the flag system for lookup tables and live
with it if a user trys deleting a row that's used by a "Deleted... " record
somewhere.
------------------------------------------------------------------------------

I'm thinking a field named "DeleletedA t" - which would be Null or contain a
timestamp and "DeletedBy" , which would be Null or contain a LAN UserID.

I'd also think that the flag sb used only at the top of hierarchical
relationships. No sense "Deleting" child records if the parent is flagged
as deleted because the user will never see same.

I would hope that there's no perceptible diff between IsNull(DeletedA t)
and IsDeleted=False . Anybody know? - although I guess I should set up a
test table with a few hundred thousand recs and try it either way....
--
PeteCresswell

[Randy Harris]

On Sat, 02 Sep 2006 12:19:11 -0400, "(PeteCresswell )" <[e-mail]>
wrote:

>Per tina:

>create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base for
all other queries.

I think you guys are winning me over.

The base query idea was the tipping point. I could use a Find-And-Replace
utility to just change all affected table names to the base query name.

Main remaining problems that I can think of are:
------------------------------------------------------------------------------
- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.

- Some sort of pseudo RI issue where a user "Deletes" a lookup table row that is
used by one or more non-deleted other table rows - causing various queries to
return Null for the looked-up value.

My kneejerk is to just not implement the flag system for lookup tables and live
with it if a user trys deleting a row that's used by a "Deleted... " record
somewhere.
------------------------------------------------------------------------------

I'm thinking a field named "DeleletedA t" - which would be Null or contain a
timestamp and "DeletedBy" , which would be Null or contain a LAN UserID.

I'd also think that the flag sb used only at the top of hierarchical
relationship s. No sense "Deleting" child records if the parent is flagged
as deleted because the user will never see same.

I would hope that there's no perceptible diff between IsNull(DeletedA t)
and IsDeleted=False . Anybody know? - although I guess I should set up a
test table with a few hundred thousand recs and try it either way....

I've personally found a tremendous amount of value in tracking user
IDs and timestamps for deletes and modifies. It's not so much as
having an answer when the finger pointing starts, though there can be
great value in those situations, as the knowledge that the information
is stored makes users much more cautious about making such changes.

Other users (and management) can easily view change history and
deleted records. Preventing problems if far more effective than
curing them.

My 2 cents worth.

-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com

[tina]

comments inline.

"(PeteCresswell )" <[e-mail] te in message
news:ru******** *************** *********@4ax.c om...

Per tina:

create a set of
base queries, one for each *data* table, with the Deleted flag set to Is
Null or False or whatever is appropriate. use those queries as the base

for

all other queries.

I think you guys are winning me over.

The base query idea was the tipping point. I could use a

Find-And-Replace

utility to just change all affected table names to the base query name.

Main remaining problems that I can think of are:
--------------------------------------------------------------------------

----

- The Admin screens where a user might try to add a lookup table row whose
unique name is already there in one or more "deleted" rows.

as long as there is a unique index on the field which holds the name values,
the user won't be able to do it. if there's a possibility that a record
could be "deleted" and then legitimately need to be "re-added" as some later
time, just write a procedure in the form's Error event to trap the
"duplicate value error" which will be generated on the unique table index.
then automatically remove the flag from that existing record and requery the
form's Recordset to display it; or you can ask the user for confirmation
first, then do it.

>
- Some sort of pseudo RI issue where a user "Deletes" a lookup table row

that is

used by one or more non-deleted other table rows - causing various

queries to

return Null for the looked-up value.

if relational integrity is enforced, that can't happen. if users are
sophisticated enough to question why a "lookup" table record can't be
deleted, especially with hundreds of thousands of records in the data
tables, then they should also understand the explanation.

>
My kneejerk is to just not implement the flag system for lookup tables and

live

with it if a user trys deleting a row that's used by a "Deleted... " record
somewhere.

well, i'm assuming you've given user the ability to "clean up" the lookup
tables because they don't want to see obsolete choices in combobox droplists
and/or listboxes. to me, the bigger issue is how to prevent data entry users
from *choosing* (even by accident) obsolete droplist options because those
items can't be deleted. if users can only *look* at historical data but
never change it, it's easy enough to flag and hide "deleted" records in data
entry form droplists. but if historical data *can* be changed, it's a
tougher issue. in that case, probably the easiest solution (from a
programming standpoint) would be to flag the records as obsolete and call a
global function to check selected options on all combobox/listbox controls'
BeforeUpdate events and block selection of obsolete values, with a msgbox
for the user.

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

----

>
I'm thinking a field named "DeleletedA t" - which would be Null or contain

a

timestamp and "DeletedBy" , which would be Null or contain a LAN UserID.

sure, why not, if it's important to track who deleted a record. just
remember that using two fields in the flag doubles the work of removing the
flag to "re-add" a record, if/when necessary.

>
I'd also think that the flag sb used only at the top of hierarchical
relationships. No sense "Deleting" child records if the parent is

flagged

as deleted because the user will never see same.

true enough. you'll have to analyze the business process (if you haven't
already) from the standpoint of determining whether specific child records
ever need to be "deleted", even though other child records and the parent
should remain active.

>
I would hope that there's no perceptible diff between IsNull(DeletedA t)
and IsDeleted=False . Anybody know? - although I guess I should set

up a

test table with a few hundred thousand recs and try it either way....

if IsDeleted is a boolean (Yes/No) field, i don't see a difference. if
you're concerned about a boolean field somehow reading as Null at the table
level, then you could set the default value of the field to False. then,
from the point of creation, the field will have a True or False value -
unless you specifically set its' value to Null (haven't tried it, i don't
know if that's possible).

hth

--
PeteCresswell

[Rick Brandt]

(PeteCresswell) wrote:

Per tina:

create a set of
base queries, one for each *data* table, with the Deleted flag set
to Is Null or False or whatever is appropriate. use those queries
as the base for all other queries.

I think you guys are winning me over.

The base query idea was the tipping point. I could use a
Find-And-Replace utility to just change all affected table names to
the base query name.

Side issue butt-in:

This is why I dislike prefixing tables with "tbl". If I were doing what you are
talking about I wouldn't have to find and replace all affected table names. I
would simply rename the table and create a query with the same name that the
table formerly had and I'm done. You don't say that you yourself use the "tbl"
prefix, but the reference to find and replace implies that.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com