Audit Trail for Access Table from Excel Form

[dramnath1981]

Dear all,


I have a new challenge before me to track changes in the Access Table which is controlled by an Excel Form. I browsed the below code and works perfectly well if the form is in Access. But I have the form in Excel. Can anyone help me in modifying the code to track changes in Excel Userform1 which is used to update data from Excel to Access?


Here is the code....


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


Thanks a lot,

Ramnath

 

[vijaySharma]

Very much possible...


I would recommend to keep the audit trail at the record level rather than Field level as per the code above.


After your excel userform is populated with the record from the access table.


You can declare a variable blnRecordDirty as Boolean.


Store the old values in a new Type so that it is easy to track; you can also use variable if you like.


After the user hits the Modify button; you can compare the values of the record against the Type / variables and if the user has made changes set the blnRecordDirty as True.


Now - Do you want to update the record with the changes the user has made; or you just want to store them in the Audit Trail table. Either way; it is possible.


Whatever method you use (record / field level tracking) you will end up have an extra table which will grow more rapidly than anticipated. Just remember Access does posses problems with larger database.


If you need more help on this; we would need visibility on the excel form and the access table to progress.


~Vijay

sharma.vijay1-at-gmail.com

 

[dramnath1981]

Hi Vijay,


I donno how to upload attachments. Otherwise I should have sent it to you. I did exactly the same way like what you told, but in a different way by not using VBA but through Excel formula. I am storing the values retrieved from Access in a second sheet first row and have a formula in the second row of the second sheet which is linked to the first sheet. In the first sheet values will change according to changes in form and if the values differ, I will have the same indicated in the 3rd row of the second sheet with now() function. So, my purpose is solved with old value (values from Access) New value (new form values) and date & time (now() function). On top of it I am taking the field names and exporting to Audit Table (Access Database) when I click submit in the form.


I know its a round about way, still my purpose is solved. You know I am not a programmer. I know very little VB and good Excel knowledge.


Thanks a lot for your time in explaining me. See you soon with more queries.


Regards,

Ramnath