Best way to track record updates?

[mrf1xa]

Hi, I;ve been asked to pull together at very short notice a database to track contracts on a short-term basis - up to 12 months. So quick and dirty will be fine for this if necessay! Apart from the obvious details like supplier details, there will be a number of fields that users can update as they work through the records e.g. cost agreed, renewal date, review completed etc

One of the requirements is that it can report on a regular basis what has changed - so the report one week may show no cost agreed, the next week there may be a cost and review completed will change to "Yes"- the report needs to show all changes and the date made.

Wondering how best to achieve this - only way I can think of is to hold the high level contract data in one table, and have another table with muliple records linked by contract ID, so a new sub-record is added each time a change is made. Is there a better way - and if not, how would I trigger the writing of the new record?

Thanks in advance.

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[kermit01de]

Hi Nigel,

what I did is to create a table named "tbl_LapLog" and to have an "after_update" event, that writes a new record to this table

Private Sub ITDSX_AfterUpdate()
CurrentDb.Execute "INSERT INTO tbl_LapLog ([LfdNr], Aktion, Zeit) VALUES (" & LfdNr & ", 'ITDS neu: " & ITDSX & "', Now())"
End Sub

So in this above example it is a checkbox named ITDSX. Whenever updated the log shows up the LfdNr (that is the internal record reference) in the next field it tells the new value for IDTSX (True or false), and finally it saves the Date/Time of change.

You may enhance that to log the logged on user as well.

To have a report just link the LapLog table to the main Lap table that holds all the detail data including the internal record ref (LfdNr) and select the fields to display.

Could that be, what you wanted?

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[hdingman]

many database management systems have a logging function that you can enable and choose what events are logged. Log CREATE/ADD, DELETE/DROP, and UPDATE/MODIFY - whatever those are in your particular dbm.