Keep a history of data changes?

[ManningFan]

This is something I haven't come across yet, but it's something I need
to do. Don't ask why, it's just got to get done.

I need to keep a table of all changes to a specific field in another
table. I've never dealt with anything like this. How would I get the
value that's in a field and then the value that it was changed to, and
pass both of them to a table? Well, I know how to pass them to the
table, but I don't know how I would store them both in variables.

Any help would be appreciated.

[Allen Browne]

Access lacks triggers, but if all changes are done in a form, you can use
the events of the form, to log the changes.

Example in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ManningFan " <ma********@gma il.comwrote in message
news:11******** **************@ 79g2000cws.goog legroups.com...

This is something I haven't come across yet, but it's something I need
to do. Don't ask why, it's just got to get done.

I need to keep a table of all changes to a specific field in another
table. I've never dealt with anything like this. How would I get the
value that's in a field and then the value that it was changed to, and
pass both of them to a table? Well, I know how to pass them to the
table, but I don't know how I would store them both in variables.

Any help would be appreciated.

[Bob Alston]

ManningFan wrote:

This is something I haven't come across yet, but it's something I need
to do. Don't ask why, it's just got to get done.

I need to keep a table of all changes to a specific field in another
table. I've never dealt with anything like this. How would I get the
value that's in a field and then the value that it was changed to, and
pass both of them to a table? Well, I know how to pass them to the
table, but I don't know how I would store them both in variables.

Any help would be appreciated.

I have encountered such a request previously. typically a kneejerk
reaction to a problem that quickly blows over - likely before your
coding is complete. Suggest you ask the requestor what kind of report
they want of the change detail and to whom they want it delivered. You
might get lucky and find out the issue has blown over before you waste
your time.

If that doesn't work, I suggest you do the work AND create the report
and deliver a month's worth of transaction change detail to the person
who asked you to make the change. ('course make sure your resume is up
to date!)

Bob

[ManningFan]

Allen -
Good stuff. I was able to adapt your code to fit my needs. Thanks!

Allen Browne wrote:

Access lacks triggers, but if all changes are done in a form, you can use
the events of the form, to log the changes.

Example in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

[ManningFan]

Bob -
Unfortunately, there is a solid business reason for this request.
Believe me, I wish half of their requests would blow over, but they're
pretty responsible end users and rarely do they ask for something I
feel is useless.

Bob Alston wrote:

I have encountered such a request previously. typically a kneejerk
reaction to a problem that quickly blows over - likely before your
coding is complete. Suggest you ask the requestor what kind of report
they want of the change detail and to whom they want it delivered. You
might get lucky and find out the issue has blown over before you waste
your time.

If that doesn't work, I suggest you do the work AND create the report
and deliver a month's worth of transaction change detail to the person
who asked you to make the change. ('course make sure your resume is up
to date!)

Bob

[Lyle Fairfield]

ManningFan wrote:

This is something I haven't come across yet, but it's something I need
to do. Don't ask why, it's just got to get done.

I need to keep a table of all changes to a specific field in another
table. I've never dealt with anything like this. How would I get the
value that's in a field and then the value that it was changed to, and
pass both of them to a table? Well, I know how to pass them to the
table, but I don't know how I would store them both in variables.

Any help would be appreciated.

If I had to do this I would move the field to another table, related to
the original table in a many-to-one relationship.
example
Spouses

ID Person NewPartner DateTime EnteredBy
12 13 MaryBeth 2006-12-27 16:38 Mandrake the Magician

The new table would comprise of its own ID field, a field for the ID of
the parent table record, a data field, a date field and a user field.
The first of these would be an autonumber, the second defaulted to the
parent record and the last two defaulted to appropriate values. So only
the data would need to be completed.
So when entering a new value a user would do so in a sub form. All the
old values would remain in the child table.
And to get current data we would use a query which would get the most
recent value from the child table.
Depending on rules about polyandry and polygamy one might or might not
require an [end date] and [end entered by] as well.