Unanswered: Deleting a record in a form but saving it on a historical table.
I have several forms bounded to tables, and whenever I want to delete a record from those forms I want the data of the deleted record to be inserted on a historical table with the deletion date.
However I am having trouble doing this, because when I click on "delete record" the current data of the form is lost and replaced with the next record, this happens before both BeforeDelConfirm and AfterDelConfirm events are triggered. In other words the event Current is triggered as soon as you press "delete record".
I have tried to create "shadow" variables that store the values of the form, but they get refreshed everytime the uses selects "delete record":
Private Sub Form_Current()
On Error GoTo ErrorHandler
'Shadow records for deletion
If Not IsNull(Me.TermID) Then
Var_id = Me.TermID.Value
If Not IsNull(Me.Term) Then
Var_Term = Me.Term.Value
If Not IsNull(Me.Comment) Then
Var_comment = Me.Comment.Value
Error_Msg = Err.Description & "Sub Form_Current()"
As a general rule, I'd leave the data in one table and include a "status" field, or just use your deletion date field to determine which are archived and which aren't. If you really want to move them, you could execute an append query and then a delete query, each using the key field to select the correct record. That of course depends on using your own button to let the user delete the record.