Report Original & Changed Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Looking for suggestions on best way to generate a report that displays a
record as it was originally listed in the table and the record after the user
makes a change. I have a form which displays the records. My thought is to
lock the records until the user marks a checkbox that selects the record.
When the checkbox is selected, all fields are saved as strOldField1,
strOldField2, etc. Then I would run a function on the BeforeUpdate event
that would save the fields as strNewField1, strNewField2, etc. Once the user
closes the form, a report would be generated that displayed unbound fields on
the report filled programatically as:

Original record: strOldField1 strOldField2
Revised record: strNewField1 strNewField2

My primary hesitation is what if the user changes multiple records? It
would seem that I'd end up with only the last record changed (?) Or maybe
there is a more direct way to get this done.

Thank you in advance for your help.
 
Jenny:

What you are really talking about trying to create here is a transaction
log. What this normally entails is creating a separate table where the
existing record is copied to before the update, (using the record ID plus a
field with a data stamp with date and time for date last modifed for last
modification as a multiple field primary key, since the record could be
updated repeatedly; its also a good idea to include in the transaction log
the id of the user that last modified the record). Then, if you want to
show the previous values, you'd query the transaction log table compared to
the current record.

This is a bit more complex that your current idea, but it is much more
robust as a way to track changes. You might do a goole search on Access and
Transaction Logs, because others have built this type of mechanism into
Access dbs and you might get some good ideas and examples as to how to
implement it in your database.
 
Back
Top