[MS_AccessPros] Audit Trail

 

I have been trying to find some sample code on this topic as I am sure there is some somewhere so as not to bother you with my specific question - but unfortunately have not been able to find it.

I have found some code for an audit trail

Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)    'Track changes to data.    'recordid identifies the pk field's corresponding    'control in frm, in order to id record.    Dim ctl As Control    Dim varBefore As Variant    Dim varAfter As Variant    Dim strControlName As String    Dim strSQL As String    On Error GoTo ErrHandler    'Get changed values.    For Each ctl In frm.Controls      With ctl      'Avoid labels and other controls with Value property.      If .ControlType = acTextBox Then        If .Value <> .OldValue Then          varBefore = .OldValue          varAfter = .Value          strControlName = .Name          'Build INSERT INTO statement.          strSQL = "INSERT INTO " _             & "Audit (EditDate, User, RecordID, SourceTable, " _             & " SourceField, BeforeValue, AfterValue) " _             & "VALUES (Now()," _             & cDQ & Environ("username") & cDQ & ", " _             & cDQ & recordid.Value & cDQ & ", " _             & cDQ & frm.RecordSource & cDQ & ", " _             & cDQ & .Name & cDQ & ", " _             & cDQ & varBefore & cDQ & ", " _             & cDQ & varAfter & cDQ & ")"          'View evaluated statement in Immediate window.          Debug.Print strSQL          DoCmd.SetWarnings False          DoCmd.RunSQL strSQL          DoCmd.SetWarnings True        End If      End If      End With    Next    Set ctl = Nothing    Exit Sub    ErrHandler:    MsgBox Err.Description & vbNewLine _     & Err.Number, vbOKOnly, "Error"  End Sub  

It works but only handles controls of the type acTextBox

When I tried to extend this to include other types of controls I have run into problems (due to my lack of knowledge in this area)

I want to include all the controls on my forms in the audit trail and I think of them as text or numeric or date etc

but there doesn't seem to be the equivalents of acTextBox for these types of controls.

If there is a better sample of an audit trail in your system please let me know how to get at it and I will see if that is any clearer, otherwise any help in improving the above code would be very much appreciated.

cheers

Phil

__._,_.___

---

Posted by: pdk444444@yahoo.co.uk

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___