Copy a record to another table before deleting the record 1

[jpkeller55]

I have a button on a form that deletes records from a table and would like to have the deleted records copied to another table prior to deleting. I am using the following code to delete the record which works fine. Can anybody help me with code that I may add to this to perform the copy function?

Private Sub cmdDelete_Click()
'Deletes current record from ReferralDetail table
On Error GoTo Err_cmdDelete_Click

DoCmd.RunSQL "DELETE FROM [ReferralDetail] WHERE [ID] = FormID"
DoCmd.Requery

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
End Sub

Thanks, Jim

 

[PHV]

DoCmd.RunSQL "INSERT INTO [another table] SELECT * FROM [ReferralDetail] WHERE [ID] = FormID"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jpkeller55]

Thanks PHV the "INSERT INTO..." statement works perfectly! Is there a way to eliminate the message box that pops up stating that one record is to be appended?

 

[PHV]

DoCmd.SetWarnings False
DoCmd.RunSQL ...
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jpkeller55]

Thanks PHV...your solutions were perfect!

 

[BakerUSMC]

PHV,

Thanks again for your posts... this was another issue I needed help on and sure enough, there you are with the answers...

BakerUSMC