Copy record on delete or edit with ADO

[uwazi]

I am trying to do this:

1. When a record is deleted from table1, I would like to store a copy of the record in another table (DelTable1), together with the > date of deletion and a comment;
2. When a record is edited I would like to store a copy of the record as it was before it was edited in another table (EditTable1), together with the date of the edit and a comment;
3. When the user deletes or edits a record a message box should appear prompting them for an optional comment, which if they type is then added to the archive delete or edit table

Presumably using the "before update" and "on delete" events on a form to launch the code would be a possibility and you could use ADO to manipulate the data? If you know how to do this I would greatly appreciate a bit of help, if not then thanks anyway.

 

[KyleS]

As far as doing what you're looking for, you are on the right track. If the table is in the same DB, you should use DAO. On the "After Update" (I tend to use this since there's no slow-down due to performance when the user is typing, only when they leave the field) you could put some code to copy each field into your table (which I'm assuming is an exact copy of the live table plus your date and comment field)

The code would look something like this:

Private Sub ButtonName_Click()
Dim dbs as Database
Dim rstEdit as Recordset

set dbs = currentdb()
set rstEdit = dbs.OpenRecordset("EditTable1&quot

rst.AddNew
rst.Fields("[Field1]&quot = Me.[Controlname].OldValue
...et cetera
rst.Fields("EditDate&quot = date()
rst.Fields("Comment&quot = InputBox ("Enter Comments&quot
rst.update

rst.Close
set rst = nothing
dbs.close
set dbs = nothing

End Sub

Hope this helps...

Kyle :

 

[uwazi]

Thanks that is brilliant. This is actually to help with an MIS for a reforestation project in East Africa - so I really appreciate your help.