Save deleted records from a form into another table

[mauricionava]

Hello, I have a form that is being used for data entry. The form fas a delete button that will erase the active record. I would like to store those deleted records into a nother table.

Would that be a append query?

Thanks.

 

[lespaul]

I would suggest you add a field to the table:

Deleted: boolean (Yes/No datatype)

when a record is marked as deleted set that to yes, set it to no as the default.

much easier to do and maintain.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Here's a couple of routines that I use

Public Sub RecordDeleted(DeleteFrom As String, DeleteTable As String, _
                         PrimaryKeyName As String, PrimaryKey As Variant, _
                         DeletedBy As String)

    Dim rs                          As DAO.Recordset
    Dim rx                          As DAO.Recordset
    Dim fld                         As DAO.Field
    Dim DeletedOn                   As Date
    
    DeletedOn = Now

    Select Case Type(PrimaryKey)
       Case dbText
          Set rs = CurrentDb.OpenRecordset("Select * From " & DeleteFrom & _
                   " Where " & PrimaryKeyName & " = '" & PrimaryKey & "'")
       Case dbDate
          Set rs = CurrentDb.OpenRecordset("Select * From " & DeleteFrom & _
                   " Where " & PrimaryKeyName & " = #" & PrimaryKey & "#")
       Case Else
          Set rs = CurrentDb.OpenRecordset("Select * From " & DeleteFrom & _
                   " Where " & PrimaryKeyName & " = " & PrimaryKey)
    End Select
    
    BuildDeleteTracker DeleteFrom, DeleteTable
    Set rx = CurrentDb.OpenRecordset(DeletedTable , dbOpenTable)

    rx.AddNew
    rx![DeletedBy] = DeletedBy
    rx![DeletedOn] = DeletedOn
    
    For Each fld In rs.Fields
        rx.Fields(fld.Name).Value = fld.Value
    Next
    rx.Update
    Set rs = Nothing
    Set rx = Nothing


End Sub

and the routine to create the table containing the deleted records is

Private Sub BuildDeleteTracker(SourceTable As String, DeleteTable As String)
    Dim tbld                        As DAO.TableDef
    Dim tblc                        As DAO.TableDef
    Dim fldd                        As DAO.Field
    Dim fldc                        As DAO.Field

    On Error Resume Next
    Set tbld = CurrentDb.TableDefs(DeleteTable)

    If Err.Number <> 0 Then

        Err.Clear
        Set tbld = New DAO.TableDef
        tbld.Name = DeleteTable

        Set tblc = CurrentDb.TableDefs(SourceTable)

        Set fldd = New Field
        fldd.Name = "DeletedBy"
        fldd.Type = dbText
        fldd.Size = 50
        tbld.Fields.Append fldd

        Set fldd = New Field
        fldd.Name = "DeletedOn"
        fldd.Type = dbDate
        tbld.Fields.Append fldd

        Set fldd = Nothing

        For Each fldc In tblc.Fields
            Set fldd = New DAO.Field
            fldd.Name = fldc.Name
            fldd.Type = fldc.Type
            fldd.Required = False
            If fldc.Type = dbText Then
                fldd.Size = fldc.Size
                fldd.AllowZeroLength = True
            End If
            tbld.Fields.Append fldd
        Next

        CurrentDb.TableDefs.Append tbld
        DAO.DBEngine.Idle dbRefreshCache

    End If

End Sub

 

[lespaul]

like I said, adding a field set to Yes or No to indicate if it's deleted is easier!!!

Les


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Remou]

I like deleted flags, however, something like this may suit a very simple application:

Private Sub Form_Delete(Cancel As Integer)
'Keep a list of deletions
'First, put them in a temporary file until delconfirm

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO DelJournalsTmp ( [Key], [TitleCode] ) " _
    & "SELECT Journals.[Key], Journals.[TitleCode] " _
    & "FROM Journals WHERE Journals.Key = " & Me.Key 
DoCmd.SetWarnings True
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
'See Form_Delete
'After delconfirm, put deletions in a deletion file

DoCmd.SetWarnings False
If Status = acDeleteOK Then
    DoCmd.RunSQL "INSERT INTO DelJournals ( [Key], [TitleCode] ) " _
        & "SELECT DelJournalsTmp.Key, DelJournalsTmp.[TitleCode]"
End If

'Clear temp file, for next time
DoCmd.RunSQL "DELETE DelJournalsTmp.* FROM DelJournalsTmp"
DoCmd.SetWarnings True
End Sub

 

[Melagan]

Cool functions guys!
Remou, why not Currentdb.Execute "SQL" for your action queries instead of

DoCmd.SetWarnings False
DoCmd.RunSQL "SQL"
DoCmd.SetWarnings True


Execute method will carry out your action query without warning, even if warnings are set true.

~Melagan
______
"It's never too late to become what you might have been.

 

[Remou]

No good reason, it is from a database that was born a good few years ago. However, when posting I say DoCmd.RunSQL partly from habit and partly so that there is a warning message, just in case.

 

[mauricionava]

Ok Remou, those codes looks very good to me but the thing is that I'm sure how to put those codes together or where to put them.

Can I have more details please?

Thank you!

 

[Remou]

They run in two events that occur for forms, the Delete Event and the DelConfirm event. You will need a deleted table (call DelJournals in the example above) and a temporary deleted table (DelJournalsTmp). These are simply copies of the table you wish to delete from, with the AutoNumber field set to Number, if one exists. After that, queries can be created in the Query design screen and the SQL can be copied.

 

[mauricionava]

ok, both tables has been created, now, where do I paste the codes you previously posted?

 

[Remou]

Into the module page of the form that you will be deleting from.