Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Thanked 0 Times in 0 Posts

    Deleted Record (2003)


    I am trying to figure out what is the best way to keep deleted records. I have been asked to keep track of any deleted records or any record that is modify and if possible by whom. What is the best way to do this?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 27 Times in 27 Posts

    Re: Deleted Record (2003)

    Love your comment about magic.

    It's a good idea though to only flag deletions.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Deleted Record (2003)

    Edited by HansV to correct stupid error in sample code

    I would add the following fields to the table:

    ModifiedDate: Date/Time
    ModifiedBy: Text
    Deleted: Yes/No
    DeletedDate: Date/Time
    DeletedBy: Text

    You can decide whether you want to display these fields in the form used to enter/edit records or not.

    Add code to the Before Update event of this form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.ModifiedDate = Now
    Me.ModifiedBy = Environ("username")
    End Sub

    Set the Allow Deletions property of the form to No. Users won't be able to really delete records any more.
    Instead, add a command button cmdDelete to the form, with caption Delete and the following code:

    Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion) = vbYes Then
    Me.Deleted = True
    Me.DeletedDate = Now
    Me.DeletedBy = Environ("username")
    RunCommand acCmdSaveRecord
    ' Optional
    End If
    End Sub

    Create a query based on the table that returns all fields, and has False in the Criteria line for the Deleted column.
    Set the Record Source of the form (and of related reports) to this query.
    The form will only display records that have not been marked as "deleted".

    You can create another query with True in the criteria for Deleted, and a form based on this query. This form is for administrative purposes, it lets you view records marked as deleted. If somebody deleted a record by accident, you can magically undelete it by clearing the Deleted etc. fields.

Posting Permissions

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