Record history upon change

[morg59jeep]

I have a table that I want to lock the data once it is entered so that it cannot be changed. The data in the table however needs to be modified when errors are found and a history of the the changes, who changed them, and why needs to be recorded. What is the best way to go about this kind of problem? I need the original data to show on certain reports and the updated data to show on other reports if possible. If this doesnt make sense please point me in the right direction of where to go or how to rephrase these questions.

 

[Remou]

This may give you some ideas:
Transaction Log for Ms. Access
faq181-291

 

[morg59jeep]

That is way over my head... can someone please explain it in easier to follow terms?

 

[Golom]

Here's the problem.

You are attempting to construct a transaction log for the table. It's easy enough to write out some kind of tracking information every time a value changes. What is substantially more complex is a mechanism to selectively reverse those changes to show the table as it was at some previous time. If you can dispense with that requirement then a simple table like
[tt]
Modifications
PrimaryKey(s)
ChangedBy
ChangeDate
ChangeReason
FieldName
OldValue
NewValue
[/tt]
would probably work and you could populate it from the Form's "BeforeUpdate" event.

 

[MichaelRed]

I hesitate to intrude here. The faq referenced is almost a "canned" routine, and requires very little understanfing. On the other hand, I am much to old and crotchy to "hold hands" during the understanding. I would gladly suggest you follow Golem's suggestion - except that he hasn't provided any functional mechanisim to implement hte population of the proposed table. further, the table -with one glaring exception- is similar enough to the sugestion in the faq that implementation would require much the same mechanisim as described therein -unless (of course) the intent is to simply expect the users to enter the information on an ad-hoc basis?




MichaelRed

 

[morg59jeep]

I have 4 different fields that I need to track the changes of. If I use Golom's suggestion would I need to create a table for each field that I need to track or would it be better to do one table like this?

PrimaryKey(s)
ChangedBy
ChangeDate
ChangeReason
FieldName1
FieldName2
FieldName3
FieldName4
OldValue
NewValue

 

[Golom]

MichaelRed

True enough ... I didn't try to get into mechanism because it's my view that such mechanisms have much to do with what "makes sense" in the user's business environment.

For example, you could implement this as a "behind the scenes" process that records the changes more or less invisibly when an authorized user alters the data.

You could also set it up so that the user goes into a "change the data" mode where they explicitly select a current value of some field and supply a reason for the change and the new value. That is then recorded in the table and "behind the scenes" the main table is also changed.

Your FAQ is of the first type and would probably serve the OP adequately provided that he has arranged data on his forms in the Checkbox, Textbox, Listbox and Combobox controls that you are examining for changes. Clearly, if the code issues SQL UPDATE commands then some more elaborate measures will be required to capture data modifications.

PS. I assume that the "glaring exception" is my handling of key fields. My proposed table is structured to handle changes to one specific table while yours will capture changes made to any table. That was deliberate because the OP asked (at least that's my impression) to track changes in only one specific table.

 

[Golom]

morg59jeep

I don't see what that does for you. With the original proposal you might have (for example)
[tt]
PK ChangedBy ChangeDate ChangeReason FieldName OldValue NewValue
XY Joe 01/01/2006 Invalid Sales 20 25
YZ Mary 02/27/2006 Revised Cost 15.99 17.99
[/tt]
I don't see what the addition of "FieldName1, FieldName2, FieldName3, FieldName4" will do for you since each field being changed is recorded in a single record. If you changed four fields on a record then four records would be generated in the "Modifications" table.

Repeating fields like that also bespeak an un-normalized table and those create problems when you try to write SQL to work with them.