By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,527 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,527 IT Pros & Developers. It's quick & easy.

Append before deleting the record

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2. Dim strSQL As String
  3.  
  4. If MsgBox("Are you sure you want to delete selected record?", vbYesNo, "Confirm") = vbYes Then
  5.  
  6. strSQL = "DELETE FROM EmployeeTbl WHERE ID = " & Me.ResultList
  7. CurrentDb.Execute strSQL
  8.  
  9. End If
  10.  
  11. End Sub 
Sep 27 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Hi,

So before the delete SQL you need an Insert Into SQL

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2. Dim strSQL As String
  3.  
  4. If MsgBox("Are you sure you want to delete selected record?", vbYesNo, "Confirm") = vbYes Then
  5.  
  6. strSQL = "INSERT INTO Mylog(COl01,Col02,COl03) SELECT COl01,Col02,COl03 FROM EmployeeTbl WHERE ID = " & Me.ResultList
  7. CurrentDb.Execute strSQL
  8.  
  9. strSQL = "DELETE FROM EmployeeTbl WHERE ID = " & Me.ResultList
  10. CurrentDb.Execute strSQL
  11.  
  12. End If
  13.  
  14. End Sub 
  15.  
Hope that helps!

:)

Best regards!
Sep 27 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.