moving deleted files on a archive tables in access using vb6

[boykendeng]

someone PLEASe HELP!!!!!!

[RuralGuy]

Maybe you'll find this link helpful: http://allenbrowne.com/ser-37.html

[boykendeng]

Maybe you'll find this link helpful: http://allenbrowne.com/ser-37.html

in my vb this is my code on my command button delete
Private Sub Command3_Click()
If MsgBox("Are you sure?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
Adodc1.Recordset.Delete
MsgBox "Record Deleted", vbInformation, "Delete"
End If
End Sub

and also i have 2 tables items and archive_items
i already have append and delete querry

what should i do next?
please help

[boykendeng]

Maybe you'll find this link helpful: http://allenbrowne.com/ser-37.html

sir i keep receiving an error user-defined is not defined

ihave a table named AutoSupply.mdb and i already created append and delete query

Private Sub cmdDelete_Click()
Call DoArchive


End Sub
Sub DoArchive()
On Error GoTo Err_DoArchive
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.


'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)


'Step 2: Execute the append.
strSql = "INSERT INTO AutoSupply ( SupplierNumber, SupplierName, Address, ContactNumber ) " & _
"IN ""C:\Users\3m\Desktop\Auto Supply\Auto Supply\DB\AutoSupply.mdb"" " & _
"SELECT SupplierNumber, SupplierName, Address, ContactNumber FROM AutoSupply WHERE (MyYesNoField = True);"
db.Execute strSql, dbFailOnError


'Step 3: Execute the delete.
strSql = "DELETE FROM AutoSupply WHERE (MyYesNoField = True);"
db.Execute strSql, dbFailOnError


'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If


Exit_DoArchive:
'Step 5: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub


Err_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
Resume Exit_DoArchive


End Sub