Copying A Record To Table It Came From

[Morris.C]

On the database we use at work, any time a Record is modified, the previous
'version' of that Record must be kept for historical and audit trail
purposes.
The new version and old are distinguished by a field called 'Active
Record'.
The code behind this, developed before I took over, is fine, until you have
a table that has a large number of fields. (Large = greater than 15!)
At the moment, a form displays the contents of the Table, and allows fields
to be modified. When the 'Update' button is clicked, the code changes the
'Active Record' field to 'No', then inserts a new Record containing ALL the
fields that the Form is displaying. (You may have only changed one field.)
If the Table has <10 fields, this is fine. But when you have a Table that
has >15 fields, this becomes tedious.

So...Is there a way for me to copy the Record being modified to the same
Table it comes from, so that the only things I need to update are the
'Active Record' field and ANY fields being changed?

Thanks

 

[John Vinson]

So...Is there a way for me to copy the Record being modified to the same
Table it comes from, so that the only things I need to update are the
'Active Record' field and ANY fields being changed?

You may want to run an Append query using the Primary Key of the
selected record as a criterion and appending it as a new record.

John W. Vinson[MVP]