Moving a record 1

[Aximboy]

I have a 2 table database with a simple form & subform.
I have an add & delete button, but instead of deleting a record, I want it to be move or transferred to a third table (all deleted records will be on this third table). So from the user's end it was deleted, but from my end I can still access those records

How do I do this?

 

[Remou]

How about:

Private Sub Form_Delete(Cancel As Integer)
'Keep a list of deletions
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO DelJournalsTmp ( ID, Field2) " _
    & "SELECT tblTable.ID, tblTable.Field2 " _
    & "FROM tblTable WHERE tblTable.ID = " & Me!ID & ";"
DoCmd.SetWarnings True
End Sub

 

[Aximboy]

Holy cow it works!!! Thank you so much.

Now, how do I add a field on DelJournalsTmp for the date its record was deleted.

 

[Remou]

Maybe:
[tt]DoCmd.RunSQL "INSERT INTO DelJournalsTmp ( ID, Field2, DateTimeDeleted ) " _
& "SELECT tblTable.ID, tblTable.Field2, #" & Now _
& "# FROM tblTable WHERE tblTable.ID = " & Me!ID & ";"[/tt]

 

[Aximboy]

Very nice! Thank you very much Remou!