Help with Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The code below was supplied by Susan Sale harkins for simple audit tracking.
I have two issues with it. Can anyone help me?
1. It only works with text box changes. I would like it to also work with
combo box changes.

2. I does insert a record for each field changed but it also inserts
anywhere from 8 to 13 additional record on fields that were not changed. The
additinal inserts slows down record process. How can i only insert a record
for only fields that have changed?

Thanks in advance

Code
Sub AuditTrail(frm As Form, recordid As Control)
'track Changes to data
'recordid identifies the pk fields corresponding
'control in form, 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
 
1)
If .ControlType = acTextBox OR .ControlType = acComboBox Then
Better yet might be replacing the IF..Then with a Select Case:

Select Case .ControlType
Case acTextBox, acCombobox, acListbox, acOptionButton
'your code
Case Else
'Do nothing
End Select

2) Not 100% sure why unchanged controls are creating records, but I have a
guess. Make sure
If .Value <> OldValue Then
Click to expand...
is
If .Value <> .OldValue Then
(note the dot before OldValue)

*If* this was indeed the problem, then I would also put "Option Explicit"
(w/no quotes) at the very top of the code module. The compiler would have
then caught a typo like that, telling you that "OldValue is an undeclared
variable" when you didn't intend for it to be a new variable, but the
existing property of a control.


HTH,
 
Back
Top