Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Deleting a record in a form but saving it on a historical table.

    Hi,

    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":

    Code:
    Private Sub Form_Current()
    On Error GoTo ErrorHandler
       
        'Shadow records for deletion
        If Not IsNull(Me.TermID) Then
            Var_id = Me.TermID.Value
        End If
        If Not IsNull(Me.Term) Then
            Var_Term = Me.Term.Value
        End If
        If Not IsNull(Me.Comment) Then
            Var_comment = Me.Comment.Value
        End If
       
    Exit_Sub:
        Exit Sub
    
    ErrorHandler:
        Error_Msg = Err.Description & "Sub Form_Current()"
        MsgBox Error_Msg
        Resume Exit_Sub
    End Sub
    Has anyone done something similar?

    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    I am not using a custom button for the deletion, I am just selecting the option "delete record" on the Home tab for it. That's how the application is designed.

    Is there no way to access the fields of the record to be deleted after you select "delete record" but before you confirm the deletion?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I always use my own button. I recalled reading a discussion by Allen Browne on using those events:

    Allen Browne - Creating an Audit Log

    Note in the pitfalls area he says you can only access the data in the delete event. In a brief test, I found that to be true.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •