Dealing With User Mistakes-Particularly Accidental Records (1 Viewer)

DeanFran

Registered User.
Local time
Today, 04:16
Joined
Jan 10, 2014
Messages
111
I've built a few small applications that are used by different small groups of people in my small organization. So far, its been sufficient for a user to tell me they made a mistake, and I go into the whatever table(s) are affected, and delete the record(s). My latest project is larger, and will have at least a couple dozen users, and I have been thinking about how best to handle it. This system has different user levels, the highest being administrator, of which there are 2. Because this db might get referenced during audits, just a simple delete is probably a bad idea. I wouldn't want an auditor to ask "why is there a gap in the record index"? My instinct is to allow admins to move a mistake to an archive table, so if the question of where the record went arises, it could be answered. To be fair, we've had a couple accidental data entries, that ended up getting saved because users didn't know pressing the Escape Key would have rectified the situation, so that's on me for not educating folks. At this point, the powers that be don't want to go full audit trail, so that's off the table. I guess my question is what is best practice, and how have others handled it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:16
Joined
Feb 19, 2013
Messages
16,553
Best practice is down to your business rules but suggest include a 'to be deleted' field which the user can populate. related queries would exclude any record that is to be deleted. You, as administrator can then review these and delete permanently if required. Also include a 'reason' field if necessary. Personally I would not move them to an archive table.

I wouldn't want an auditor to ask "why is there a gap in the record index"?
hope you are not relying on the autonumber field for this because if the user presses the escape key, then that number is lost
 

DeanFran

Registered User.
Local time
Today, 04:16
Joined
Jan 10, 2014
Messages
111
hope you are not relying on the autonumber field for this because if the user presses the escape key, then that number is lost

I did not know this, nor did the person who was insisting that gaps in the auto number sequence would be a red flag should an auditor stumble across it.

Your proposed solution would work in our case, I think, and be pretty easy to implement. Thank you for the information.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:16
Joined
Feb 19, 2013
Messages
16,553
users should not see autonumbers, they are just a unique identifier for a record. I did have a link about what they are and are not, but not working now, suspect the page has been moved - perhaps someone else knows where it is?

auditors might expect to see a consecutive number range for invoice numbers for example, but for this you would use something like dmax+1.
 

DeanFran

Registered User.
Local time
Today, 04:16
Joined
Jan 10, 2014
Messages
111
I often have the auto number displayed for my own use on forms and reports, and I confess I'm guilty of not always going back and hiding all of them. I have recently started the rule of never having visible to users from the start.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:16
Joined
Feb 19, 2013
Messages
16,553
thanks - my link was slightly different! Have updated
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:16
Joined
Feb 28, 2001
Messages
27,001
When dealing with auditors who look at the autonumber field, you need to tell them that the number in question is not guaranteed to be contiguously numbered. An autonumber is only guaranteed to be a unique and totally arbitrary number to assure that two records have different primary keys. They are an example of what is called a SYNTHETIC key (as opposed to a natural one). Since they are synthesized by a machine, they have NO INTRINSIC MEANING. Only people-filled fields have meaning.

On a different question, it takes a bit of work but if you want to REALLY do this in a way to force users to think about what they are doing, here is how I handled "careless save" situations.

First, users NEVER saw raw tables or queries in datasheet view. EVERYTHING was done piecemeal through forms. My forms were set up so that tabbing would cycle through the fields of the form; tabbing from the last field would return to the first field of the same form and records - i.e. no hidden navigation. Also, when the form became "dirty" because of a change to the contents of any live control, I turned off the navigation buttons. Clean forms, you could navigate. Dirty ones? You had to resolve whatever was going on before you could do anything else.

I used a form's "BeforeUpdate" event to be the "guard dog" for what was going on. There was one and ONLY one way to save a record and that was to hit a SAVE command button that would issue the DoCmd.Save function. (Hint: Button wizards can build this for you from the list of pre-defined command button operations.) The user could also use a CANCEL command button (wizard calls it an UNDO function). But if the user tried to navigate, it would be an implied SAVE and the "BeforeUpdate" Cancel option would trap that as an unauthorized operation (in that context). Closing the form implies a SAVE, so the BeforeUpdate event trap would cause that to get stopped, too.

As to the question of deletions? We had auditing rules that disallowed deletions except according to a precise procedure. We marked a record for deletion. We made an audit entry at the time in a separate auditing table in order to record the reason. Since we knew ahead of time that such a deletion would be possible, the flag for "marked for delete" wasn't a Yes/No, but rather was a BYTE integer. We encoded the various reasons for removal, and it was no accident that the code of 0 meant "not marked for deletion." We had maybe half-a-dozen codes for removal. One was "human error" but we also had "obsolete" and "due for archiving" and "directed by management" and a couple of other obscure cases.

We didn't delete the record until the end of the third full month after it was marked for deletion. What we did was copy it to an external archive database after which we deleted it from the active database. In that specific order, so no data would be lost. And as far as the archive database, all we had to do was to make a yearly copy of that database (with the year as part of the new name) and then erase the archive table. We had several years of data stored that way.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:16
Joined
Feb 19, 2002
Messages
42,981
Best practice goes back to BEFORE the invalid record gets added. You should actually prevent the addition rather than worrying about whether you should hide it or delete it. At the table level, some fields should be defined as required. Access will then prevent any record from being saved where those fields are null. In the case of text fields you should also change the Allow ZLS property to No. It is just more complication to deal with both null and Zero Length Strings.

Then in the BeforeUpdate event of the form, you do additional validation. Perhaps some fields are required only if other fields have a certain value. Perhaps there are date constraints. It certainly doesn't make any sense to allow future date of birth values. Employees are probably not going to be normally older than 70. Although, in this case, you would want a warning rather than actual prevention.

When a validation rule fails, you cancel the update and that prevents the record from being saved:
Code:
If Me.DOB > Date Then
    Msgbox "Date of Birth must be <= today.",vbOKOnly
    Me.DOB.SetFocus
    Cancel = True
    Exit Sub
End If
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:16
Joined
Feb 19, 2013
Messages
16,553
Since we knew ahead of time that such a deletion would be possible, the flag for "marked for delete" wasn't a Yes/No, but rather was a BYTE integer. We encoded the various reasons for removal, and it was no accident that the code of 0 meant "not marked for deletion." We had maybe half-a-dozen codes for removal. One was "human error" but we also had "obsolete" and "due for archiving" and "directed by management" and a couple of other obscure cases.
Like that idea
 

Users who are viewing this thread

Top Bottom