Update one table when another table changes

[cgclower]

Hello!



I have a database with two tables. I am trying to update one of the tables every time a record is added to or deleted from the other table. Right now, I am planning on using this VBA code:

 
Sub Form_AfterInsert()
 
DoCmd.RunSQL "INSERT INTO  SecondTable VALUES([some value from FirstTable], [another value from FirstTable])"
 
End Sub

My question is how do I get the values from the first table after they are inserted/before they are deleted? I was hoping for some sort of ActiveRecord function that would have that data, but I am not finding anything like that.

Thoughts? Advice?

Thanks for the help!

Update: I guess adding an account could be tracked in the same table, but I can't keep track of the deletions there. I am looking for a way to save information out of a record that is about to be deleted. This make sense? Thanks!

[orange]

A technique is to not actually delete records. If you have a field in that table, say "QuasiDelete" or something that is a boolean, you simply update that field to Yes indicating "Deleted".
You adjust other code to always check the QuasiDelete field. We used to call this ShadowDeletes in the old days, but you always had a copy of a "deleted" record.... just in case.

Another approach is to have a table that records the record deleted, time of deletion, etc. Just for recovery/audit purposes.
Good luck.

[cgclower]

Another approach is to have a table that records the record deleted, time of deletion, etc. Just for recovery/audit purposes.
Good luck.

Yes, that is exactly what I want to do!

Um...any idea how to go about that?

Thank you!

[rpeare]

your SQL statement won't work

when you're doing an insert it should look like

INSERT INTO <tablename> (<field1>, <field2>, etc) VALUES (<value1>, <value2>, etc)

Where the values would have to be enclosed by ' marks for text fields.

Secondly what are you actually trying to accomplish by updating the second table? deleting records from a database is permanent and I really would not encourage anyone to do it with code. Instead as orange has said add a marker that is basically a void which you can exclude on all data entry, forms, reports etc.

I would like to know why you are trying to do this, are you attempting to create a log of who made changes to records in your database?