Record Archive using Document Number on a Form

[Basil]

How do I copy a record to an archive table whilst I have the record open on a form? I want to use the document number of the record I am viewing as the criteria for archiving it. I don't want to delete the record, I simply want to copy and paste it into another table. I then want to be able to change/update field values on the record I have open i.e. creating a new version. I would need to archive the record before updating any fields.

[isladogs]

The simple answer is DON'T!
Doing so would mean duplicating data
A better approach is to have a Yes/No field in your table called Active with default value =True
When you want to archive a record, set the field false

If you really must send the data to another table, use an append query, then delete that record from the first table

[NoellaG]

Hi, I suppose you want a history table which reflects all changes mad to the records. Be aware this could make explode the size of the database, so I would advise to keep the history table in another database.
- Add all fields you want to track to the history table, together with some metafields like createdOn, createdBy, ChangedOn, ChangedBy, VersionDate. In the original table you add the fields createdOn, createdBy, LastChangedOn, LastChangedBy.
- in the afterupdate event you can fill the metafields LastChangedBy, and LastChangedOn, so they are triggered even when the archive button is not clicked.
- In the Onclick event of the archive button write a code that triggers an append query or uses an ADODB.command to copy the record to the archive table. in the WHERE clause you can put something like "..... where ID = " & me.txtID. This assumes that the ID is a number and the field is bound to a txtID textbox on the form.
- Click the button to archive the current version
-Go on changing the actual record
- Don't forget to clean out your archive database from time to time.

[June7]

If you want an audit trail, review http://allenbrowne.com/AppAudit.html

If you really want to archive, review http://allenbrowne.com/ser-37.html