Audit Trail (history) of a field

[Christek]

Hi,

is there any possibility to create audit trails in MS access? (If user A changes a value in field X, after that user B changes the value again => for that I need to have a history)

Do you have any idea?

Thanx a lot,
christek

 

[jdegeorge]

You can do this, but it can be tricky. I only keep track of the LAST person's ID who changed a record with the date. I don't keep a history because it would involve storing the current field value to a temp variable in the field's BEFORE UPDATE property, and if the change is committed, you have to save the temp variable with the ID and change date to another table. It's a lot of work. I believe I once saw some code on

http://www.mvps.org/access.

Good luck.

Jim DeGeorge

 

[Uriel365]

Hi I use this in a database I manage.I create in the basDeclarations module enough Global Variables to hold the information needed to be audited. I then use this code behind the after update event of each field I wish to audit.

glbFieldName = "Name of Field"
glbPreRecord = txtYourFieldName.OldValue
glbPostRecord = txtYourFieldName.Value


Call AuditTrail

----------------------AuditTrail---------------------------
Public Function AuditTrail()

Dim rstAudit As Recordset

Set rstAudit = CurrentDb.OpenRecordset("tblAudit&quot

With rstAudit
.AddNew
!FieldName = glbFieldName & " "
!PreRecord = glbPreRecord & " "
!PostRecord = glbPostRecord & " "
!User = CurrentUser
!Date = Date
.Update
End With

rstAudit.Close

End Function

Hope this helps.

Paul

 

[jdegeorge]

Paul

What happens if the user UNDOES the changes before leaving the record? Do you have a way to remove the edits from the audit database because they're not needed now?

Jim DeGeorge

 

[MichaelRed]

see faq-181-291




MichaelRed
m.red@att.net

Searching for employment in all the wrong places