Having an audit trail

[ryanmce92]

Hi Folks,

After much trekking on the internet I have managed to find an audit trail code which works perfect with my database. However I have been having one slight issue so if anyone could help me I would greatly appreciate it.



I have the below code which works for text boxes but for some reason when I use check boxes or combo boxes it does not seem to track these changes, if any of you could point me in the right direction that would be great!.

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

Please bare with me if I have a question or two kind of a newbie at this sort of thing so any help would be greatly appreciated!!

[ranman256]

I cycle thru looking for my objects using TYPENAME...

Dim ctl
For Each ctl In Controls
  Select Case TypeName(ctl)
    Case "CommandButton"
       MsgBox ctl.Name, , "button"
       
    Case "ComboBox"
      MsgBox ctl.Name, , "combo"
  End Select
Next

[pbaldy]

The right direction is:

If .ControlType = acTextBox Then

which limits the code to textboxes. You can use ranman's method or add to the above with OR.

[ryanmce92]

The right direction is:

If .ControlType = acTextBox Then

which limits the code to textboxes. You can use ranman's method or add to the above with OR.

How would I add to the above with OR ?

I have this solution working now and its exactly what im looking for so if I could get that working in any way it would be perfect.

[ItsMe]

You can start with this line of code

If .ControlType = acTextBox Then

And determine which constants to use
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

I think that list should work combos and such for more current versions of Access. Maybe use the OR operator to include additional control types.

something like
If .ControlType = acTextBox or .ControlType = acComboBox Then

[pbaldy]

How would I add to the above with OR ?

Like ItsMe showed.