Creating a history table 1

[K3ith]

Can anyone help please,

What it is I am trying to record changes made to a tables data, I have two tables tbloriginal and tblHist. What I would like to be able to do is that every time a record is changed within tbloriginal the old record (before update) is written to the tblhist table. I would like to achieve this through the use of a button on a form, for example the button would display save, but when the button is clicked it writes the old values of the record being altered to the table called tblhist.

So for example if tbloriginal has the following three fields ID, forename, surname. And there is a record such as ID= 1, Forename = Fred, Surname = Bloggs. And someone changes the surname to Brown then the original ID = 1 Forename = Fred, Surname = Bloggs is written to the table tblhist, along with the date when the change was made.

I have read through the forums and have seen information regarding creating history tables, but do not find they answer my question.

So if any one could help this would be greatly appreciated.

Thanks.

 

[MichaelRed]

Partially do not SPECIFICALLY answeryour request, because it is a rather wasteful approach, and it does not save some important information. The MUCH more useful approach is to save the field name, the old value, the User and the date/time of the chage. Some of the more paranoid also save the new value. From the (above list of) saved information, the entire transaction sequence of changes can be easily recovered, including not just the what, but also the who and when. AND, the recordset necessary to do so is generally smaller than the entire record reproduction. Of course, in either instance, the audit only works (in Ms. A. when you have and enforce strict security and permit users to ONLY use the proper forms for their data manipulation.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Rolliee]

YOu could probably do an INSERT.

SQL = "INSERT INTO histable (me.fields(0),...)-
VALUES ( field1, field2...);"

docmd RunSQL SQL

You will have to play around to get the fields iin the right form. Do a msgbox SQL first and be sure that it is right then grind away...

R