Help With Module

[thabounceisback]

Applying Audit trail Module to Multiple Controls in a Form

Greetings!

I have a module that is designed to create an audit trail for me. Here is the code below:

Option Compare Database

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.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    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 Select
End With
  Next
  Set ctl = Nothing
  Exit Sub


ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

I am using the Forms Before Update event with the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, EmpCmb)
End Sub

to call the module into action.

However, I cannot figure out how to write the code so that it will audit several different controls on the form.


The controls I want it to audit are EmpCmb, Hours_Missed, DscCmb, and NtfCmb. Also, if possible, I would like it to audit my date picker control (MSComCtl2.DTPicker.2); although this is less important.

I have only been able to audit one control at a time. What am I doing wrong? What is my first step?

 

[RuralGuy]

The code you have is designed for only one control. Perhaps a different approach would be in line. Look under Utilities.

 

[Access_guy49]

Have you tried:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, EmpCmb)
  Call AuditTrail(Me, Name of second control)
  Call AuditTrail(Me,Name of third control)
  etc....
End Sub

 

[thabounceisback]

Have you tried:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, EmpCmb)
  Call AuditTrail(Me, Name of second control)
  Call AuditTrail(Me,Name of third control)
  etc....
End Sub

Ok, I tried this and it does work, but it stores each change in the audit trail table four separate times.

 

[Access_guy49]

Yes, that's because you run the entire function each time, and each time it runs everything and inserts a new record into your table using the specified parameter.

If you want it to be placed in a specific field, your going to have to change your code so that it takes each variable, and places it into the appropriate field.

When you build your string, Your only parameter you use is done here

& cDQ & recordid.Value & cDQ & ", " _

So you'll need to add more variables in your sub, and then add those variables into the sub routine.

You may want to place a stop near the top of your code and actually step through it once or twice to watch what it's doing.

 

[jdraw]

Yes, that's because you run the entire function each time, and each time it runs everything and inserts a new record into your table using the specified parameter.

If you want it to be placed in a specific field, your going to have to change your code so that it takes each variable, and places it into the appropriate field.

When you build your string, Your only parameter you use is done here


So you'll need to add more variables in your sub, and then add those variables into the sub routine.

You may want to place a stop near the top of your code and actually step through it once or twice to watch what it's doing.

As Access_guy49 says, you need to adjust your code.
You will need a name, old value , new value field in your table for each control you are interested in.

It seems to me that this
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " is info that identifies the record, and

you maybe should consider a second SQL string like this which has the Values for EditDate,User....which are record values
strSQL1 = "VALUES (Date()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", "

but you will need to add fields for

For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox, acComboBox
If .Value <> .OldValue Then
i = i +1
varBefore = .OldValue
varAfter = .Value
strControlName = .Name

you'll have to extend the strSQL something like

strSql = strSQL & " SourceField" & i & ", BeforeValue, AfterValue "
and
strSql1 = strSQL1 & cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ
for each control you're interested in.

Then, you have to make the SQL syntax legit.
strSQL = strSQL & ")" ***you may have to remove a trailing comma first***
strSQL1 =strSQL1 & ")" and
strSQL = strSQL & strSQL1
then do your Debug.print stuff

This is not tested code - just off the top of my head - but should give the idea.

 

[Access_guy49]

Jdraw...Thumbs up! I like it... i like it a lot!

 

[thabounceisback]

Jdraw...Thumbs up! I like it... i like it a lot!

Yes. Thank you all very much!