To Delete Or Not To Delete

[EndersG]

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

CurrentDb.Execute ("INSERT INTO tblArchives (SELECT * FROM tblActive WHERE ID = " & Me.ID & ")")

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Above is a delete function I created on a Form using the command button wizard in MS Access 97. The only additional line of code you will find is an action append query designed to add a record from one table (tblActive) to another table (tblArchive) just before the record is deleted from the active table. One problem: The user who invokes this command is warned whether he/she wants to continue with the delete action. How can I code this procedure to continue with the append action if the user selects YES but rollback the append action if he/she chooses NO?

Thanks for your help.

 

[pono1]

E,

Something like the following should work.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim Answer As VbMsgBoxResult

Answer = MsgBox("Are you sure you want to delete " & _
"this record?", vbQuestion + vbYesNo, _
"To delete or not to delete")

If Answer = vbNo Then
MsgBox "Cancelled. Record not deleted", _
vbExclamation + vbOKOnly, "Coward!"
Exit Sub
End If

DoCmd.SetWarnings False
'Append rec
Append query code goes here...

'Delete rec
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.SetWarnings True

MsgBox "Record deleted", vbExclamation + vbOKOnly, "Nice Job"


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Regards,
Tim

 

[EndersG]

Much appreciated, Tim.

 

[pono1]

No problem...

Tim