Append before deleting the record

[kamle_b]

I know it is best said to archive records when you press delete button. I need help with this.

I have a list box where filtered data is showed. The user highlights the record and clicks on delete button to delete the record. This at the minute completely takes off that record. How do i back-up this data so that on clicking delete button it will remove the record from list box but append to other table called tblBackup?

I have tried making append query which i tried to put the code in vba, but with no success.

code currently on delete button is as follows:

Private Sub cmdDelete_Click()
Dim strSQL As String
 
If MsgBox("Are you sure you want to delete selected record?", vbYesNo, "Confirm") = vbYes Then
 
strSQL = "DELETE FROM EmployeeTbl WHERE ID = " & Me.ResultList
CurrentDb.Execute strSQL
 
End If
 
End Sub 

[PEB]

Hi,

So before the delete SQL you need an Insert Into SQL

Private Sub cmdDelete_Click()
Dim strSQL As String
 
If MsgBox("Are you sure you want to delete selected record?", vbYesNo, "Confirm") = vbYes Then
 
strSQL = "INSERT INTO Mylog(COl01,Col02,COl03) SELECT COl01,Col02,COl03 FROM EmployeeTbl WHERE ID = " & Me.ResultList
CurrentDb.Execute strSQL
 
strSQL = "DELETE FROM EmployeeTbl WHERE ID = " & Me.ResultList
CurrentDb.Execute strSQL
 
End If
 
End Sub 
 

Hope that helps!

:)

Best regards!