Creating a log file of amendments

Hi all,

I am trying to create a log file of amendments to a particular table of records.

My thinking on this is as follows.

1. I choose a record to amend, i.e. the contents of either one or more fields from TBL_main
2. copy that record to a temporary table TBL_temp
3. copy that record to the log table (as the record to be amended as its original state. TBL_log
4. Amend the record in the temporary table with the new data.
5. Update TBL_main with the new record
6. Copy the amended record to the TBL_log.

This way I end up with the log table with the two records i.e. the original and amended record
and the TBL_main is updated with the new amended record. I had planned to do this using queries and macros to achieve this somehow. However I am stuck on point one whereby I have to isolate the initial record itself!

I am also wondering if there is an easier way. Probably in VBA.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
The Defacto stardard:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You are making this more complicated than it needs to be.  The only time I would ever use a staging table is if I needed to add/modify a set of rows and all the rows had to balance or be valid before I could apply them to the db.  Access doesn't save changes after each field is updated so the changes are all committed at once immediately following the form's BeforeUpdate event.  Of course a user could change fldA, save the record and then change fldB.  In that case, you would end up with two separate log records.  You also don't need to copy both the before and after images to the log table since the "after" image will be identical to the next "before" image.  I'm not sure which method Allen recommends but I would save the "after" image in the AfterUpdate event.  This method requires a one time copy of all current records to the log table otherwise you will never have a copy of the current values.  I prefer the After image process since it is easier to control.  You don't log anything until AFTER the update is committed so you don't have to worry about backing out the log record if an update is cancelled.

Presumably, you will be using a form to do the adds/changes.  That means it is the current record that you are logging and you always have the unique ID of the current record so the actual logging is done with an append query that selects the current row using it's unique identifier.  The After method also allows you to do physical deletes since you can create a "delete" record in your log.  Don't forget that the log table needs an autonumber for uniqueness and the primary table's autonumber is stored as a long integer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.