Duplicating a Record in a Table

[rfear]

I have a table that contains financial data on projects. Each project has one record consisting of numerous fields.

I want users to be able to archive off copies of these records at certain points in time so I can track the financial data - a bit like storing different versions or issues.

On any one day I want users to be able to access the live record and review the archive.

Question : Is there a simple way of duplicating a record in a table ?

At the moment I am using a form by copying all of the field values from this form, moving to a new record, writing the field values back to the form hence creating a copy. This seems somewhat laborious.

Can't I access the table directly somehow and simply cut and paste ?

Also, is there a simple way to check for a duplicate record before cloning the record i.e. if the data has already been archived once don't do it again.

 

[ajetrumpet]

At the moment I am using a form by copying all of the field values from this form, moving to a new record, writing the field values back to the form hence creating a copy. This seems somewhat laborious. Can't I access the table directly somehow and simply cut and paste ?

How about something like...

docmd.runcommand(accmdselectrecord)
  docmd.runcommand(accmdcopy)
    docmd.gotorecord(acdataform, "formname", acnewrec)
      docmd.runcommand(accmdpaste)

Also, is there a simple way to check for a duplicate record before cloning the record i.e. if the data has already been archived once don't do it again.

If you have a unique identifier for each record in the table, you could use a DCOUNT before the commands to check and see if there is a count of ">1" for that field value.

 

[pbaldy]

Just to offer an alternative, I probably wouldn't copy the record. I'd add an audit function so any changes to the record would be tracked.

 

[rfear]

How about something like...

docmd.runcommand(accmdselectrecord)
  docmd.runcommand(accmdcopy)
    docmd.gotorecord(acdataform, "formname", acnewrec)
      docmd.runcommand(accmdpaste)

Yay, 104 lines of code down to 4, thanks.

 

[ajetrumpet]

Yay, 104 lines of code down to 4, thanks.

Wow! I didn't know I was that good!