Undo changes

[The_Doc_Man]

I would like to question what the reason for an unintentional modification is.

Having worked as a tier III support person (as one of many duties) for the U.S. Navy Enterprise Data Center, I can tell you the simplest reason: PICNIC. That was our acronym for "problem in chair, not in computer." People do things and then realize .... "Whoops!" When your staff includes a low-wages clerk who hasn't gone through college but instead gets a REALLY short introductory session on how to use a computer, you have to take into account that they WILL screw up. You can put a LOT of smarts into your application, but the end result will still be that end users are THE BEST stress test you can find for any code. Murphy's Law is based on the simple fact that your end users are humans and humans make mistakes.

 

[Pat Hartman]

Maybe unbound form with update queries

The OP is talking about backing out a change AFTER the record has been saved. You are suggesting methods to stop a record from being saved. That is what validation in the BeforeUpdate event is for.

You can never protect a user from entering the wrong value as long as that value falls within the rules for the data field.

 

[MajP]

@zelarra821
you changed the requirement, so you need to be clear. Your initial post was about moving to another field in the same record. Your second post was moving to a new record.
I provided a simple solution for the first problem.
In the second problem you can wrap a continuous form in a transaction, but that still undos all changes.
The only way to reverse a single value on a previous record is doing what @Pat Hartman suggests and create a change log. That is some work but not overly complicated. There are good examples of logging on the web.

 

[MajP]

A simple solution for tracking changes to Access data | TechRepublic

When you have a lot of users changing data, it's easy for things to go wrong. But if you keep an audit trail of data input changes, you'll be able to determine which operators are consistently making mistakes (or not). You can also quickly fix problems before erroneous data wreaks havoc. This...

www.techrepublic.com

Once you build the audit trail it would be trivial to build a pop up to pick a field and get a list of previous values. Then click the value you want and update.

 

[Pat Hartman]

The problem with that solution is that it does not take into account updates that get cancelled due to bad data. If you use a solution like this, the code in the BeforeUpdate event needs to append the change records to a temp table (preferably in a "side" BE to avoid bloat). Then in the AfterUpdate event of the form, copy the temp table data to the actual log table and delete the data in the temp table. I would also modify it to log new records and deletes as well. You also need code to delete the temp table rows if the BeforeUpdate event gets cancelled.

 

[MajP]

Harder to implement but Allen Browne's version accounts for most of that.

Allen Browne - Creating an Audit Log

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

allenbrowne.com

 

[Edgar_]

If you had:

tbPeople:
    PersonID
    FirstName
    LastName

You could have a replica:

tbPeopleHistory:
    Id
    PersonID
    FirstName
    LastName

Then, using an After Update table macro, you can make it Create a record and Set its fields. With that, each modification to your record is inserted in the history table and you'd have a history of changes that you could browse using a subform. It's a friendly approach.

Edit: grammur and stuff

 

[zelarra821]

I have created a Configuration form where the user can create backup copies and select the number they want to have. When you activate it, once you close the database, a file is created with the date and time, and the oldest is deleted to leave the number of copies selected.

This is the solution I have proposed.

 

[Pat Hartman]

Saving full copies of a database is very different from an audit trail. One is not a substitute for the other. What is the business need you are trying to satisfy?

Generally full file backups are the province of your IT department. They are the people who set up the backup and retention cycles for each server. Typically, you would set some parameters and they would implement the schedule -- 14 daily backups, 8 weekly backups 24 monthly backups, 7 annual backups. That would protect against catastrophic losses but not user error. Audit logs are usually used to recover from user error. Charlie realizes he changed the wrong record so he asks you to recover the last change to the record. If the last change was made prior to the most recent backup, there is a good chance you can find the old value in one of the backups but if the most recent change was this morning, there is no backup that will help you and not only that, you won't know the record was changed earlier today before Charlie clobbered it.