Update one table when another changes

[cgclower]

Hello!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a database with two tables (well actually a lot more, but only two that matter for this question). I am trying, via VBA, to update one of the tables every time a record is added to the other table. Right now, I am planning on using this command:
<o></o>

Sub Form_AfterInsert()
<o:p></o:p>
DoCmd.RunSQL "INSERT INTO SecondTable VALUES([some value from FirstTable], [some other value from FirstTable])"
<o:p></o:p>
End Sub

<o></o>
My question is how do I get the values from the first table after the record was inserted? I was hoping for some sort of LastRecordInserted function that would have that data, but I am not finding anything like that.
<o></o>
Thoughts? Advice? Taunts and laughter?
<o></o>
Thanks for the help!
<o></o>

 

[Joe4]

I guess my question would be to find out if this is really necessary. Most of the time, if a database is designed well and normalized, this sort of thing usually isn't necessary (just like any calculation that can be done in a query should not be stored in a query).

So, can you explain exactly what you are trying to do, and why it needs to happen like this?

 

[cgclower]

I have a table with a bunch of account information in it. When someone adds or deletes an account from that table, I want to show the account number and "Created" or "Deleted" somewhere, along with the date and user who deleted it. I guess adding an account could be tracked in the same table, but I can't keep track of the deletions there. Can I?

 

[Joe4]

One idea is to change how it works. Instead of actually deleting the data, perhaps you can add a checkbox that they would check to "delete" the data (but it really wouldn't delete it, just hide it from view). Then you could have VBA code that is triggered when this check box is selected to update your datestamp flag.

I don't know how your database is designed, but in this type of scenario, you would want the database to be a form-driven database, which means that you direct all activity through forms so users don't go into the tables directly. That way you can control how all this works, especially with updating date stamps.