Solved: Copying Records

[jauner]

I have a database where when the user wants to make updates they click a change button. I want that button to make a copy of the current record and give it a different ID making it a new record basically. How can I do this?

[geekgirlau]

Create an append query where the source data and the append tables are one and the same, with a parameter to identify the ID of the record. Once the record has been updated, run the query and set the parameter to the ID of the record that has just been updated.


Dim db As DAO.Database
Dim qdf As DAO.QueryDef


Set db = CurrentDb()
Set qdf = db.QueryDefs("qapp_CopyRecord")

With qdf
.Parameters("ID") = 1

DoCmd.SetWarnings False
.Execute
DoCmd.SetWarnings True
End With

ExitHere:
On Error Resume Next
qdf.Close
db.Close

Set qdf = Nothing
Set db = Nothing

[jauner]

Ok I have a related question. I got that part to work but there are subform related and creates new records for those. I need to update the new record with what was in the originally record. Example Record A is copied to Record B. I need to fill the subform for new record B with what was in Record A subform data. I am keeping track of the original ID so I can look it up.

[jauner]

I found a site that had the answer i needed. This link will create duplicate records for form and related subform.

http://allenbrowne.com/ser-57.html