Delete Records

[John]

Hi,
I have a command button which runs append queries on the current record of a
form/table. The append queries copy the current record to a backup set of
tables. On completion of this backup I would like the current record to be
deleted from the active form/table using the same command button.

How can I accomplish this?

 

[Brendan Reynolds]

It's best to do this kind of thing within a transaction, which ensures that
the deletion happens only if the append completed successfully. Here's an
example ...

Private Sub Command0_Click()

Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim fInTrans As Boolean

On Error GoTo ErrorHandler
Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
fInTrans = True
Set db = wsp.Databases(0)
db.Execute "INSERT INTO TargetTable (TestText) VALUES ('" & Me.TestText1
& "')", dbFailOnError
db.Execute "DELETE * FROM SourceTable WHERE TestID = " & Me.TestID,
dbFailOnError
wsp.CommitTrans
fInTrans = False
Me.Requery

ExitProcedure:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
If fInTrans Then
wsp.Rollback
fInTrans = False
End If
Resume ExitProcedure

End Sub

 

[John]

Thanks Brendan

Regards

John

It's best to do this kind of thing within a transaction, which ensures
that the deletion happens only if the append completed successfully.
Here's an example ...

Private Sub Command0_Click()

Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim fInTrans As Boolean

On Error GoTo ErrorHandler
Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
fInTrans = True
Set db = wsp.Databases(0)
db.Execute "INSERT INTO TargetTable (TestText) VALUES ('" &
Me.TestText1 & "')", dbFailOnError
db.Execute "DELETE * FROM SourceTable WHERE TestID = " & Me.TestID,
dbFailOnError
wsp.CommitTrans
fInTrans = False
Me.Requery

ExitProcedure:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
If fInTrans Then
wsp.Rollback
fInTrans = False
End If
Resume ExitProcedure

End Sub