title: Audit trail in Access 2010 database
body: <p>I am trying to create an audit trail in an Access 2010 database.  I found some code on www.wvmitchell.com and it works well except for one issue.  It records records that are updated but not new records or deleted records.  It is very important that those are recorded. The following is the information and code that I used:  </p>
  
  <pre><code>Option Compare Database
  Option Explicit
  
  Sub TrackChanges(F As Form)
  Dim ctl As Control, frm As Form
  Dim MyField As String, MyKey As Long, MyTable As String
  Dim db As DAO.Database, rs As DAO.Recordset
  On Error Resume Next
  Set frm = F
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tbl__ChangeTracker")
  With frm
      MyTable = .Tag
      ' find the primary key &amp; its value, based on the Tag
      For Each ctl In .Controls
          If ctl.Tag = "PK" Then
              MyField = ctl.Name
              MyKey = ctl
              Exit For
          End If
      Next ctl
      For Each ctl In .Controls
          ' inspect only data-bound controls
          Select Case ctl.ControlType
              Case acTextBox, acComboBox, acCheckBox
                  If Nz(ctl.ControlSource, "") &gt; "" Then
                      ' if changed, record both old &amp; new values
                      If Nz(ctl.OldValue, "")&lt;&gt; Nz(ctl, "") Then
                          rs.AddNew
                          rs!FormName = .Name
                          rs!MyTable = MyTable
                          rs!MyField = MyField
                          rs!MyKey = MyKey
                          rs!ChangedOn = Now()
                          rs!FieldName = ctl.Name
                          If ctl.ControlType = acCheckBox Then
                              rs!Field_OldValue = YesOrNo(ctl.OldValue)
                              rs!Field_NewValue = YesOrNo(ctl)
                          Else
                              rs!Field_OldValue = Left(Nz(ctl.OldValue, ""), 255)
                              rs!Field_NewValue = Left(Nz(ctl, ""), 255)
                          End If
                          rs!UserChanged = UserName()
                          rs!CompChanged = CompName()
                          rs.Update
                      End If
                  End If
          End Select
      Next ctl
  End With
  rs.Close
  Set rs = Nothing
  Set db = Nothing
  End Sub
  
  Private Function YesOrNo(v) As String
      Select Case v
          Case -1
              YesOrNo = "Yes"
          Case 0
              YesOrNo = "No"
      End Select
  End Function
  </code></pre>
  
  <ol start="3">
  <li>A table to store the results. For text fields, I've indicated the length in the Description:</li>
  </ol>
  
  <p>Here is a VBA module that will create the table for you.</p>
  
  <pre><code>Option Compare Database
  Option Explicit
  
  Sub Create_tbl__ChangeTracker()
      Dim db As DAO.Database
      Dim fld As DAO.Field
      Dim idx As DAO.Index
      Dim tdf As DAO.TableDef
      '
      Set db = CurrentDb
      Set tdf = db.CreateTableDef("tbl__ChangeTracker")
  With tdf
      ' ID is AutoNumber and Primary Key
      Set fld = .CreateField("ID", dbLong)
      fld.Attributes = dbAutoIncrField
      .Fields.Append fld
      Set idx = .CreateIndex("ID")
      idx.Fields = "ID"
      idx.Primary = True
      .Indexes.Append idx
      '
      ' add remaining fields
      Set fld = .CreateField("FormName", dbText, 64)
      .Fields.Append fld
      Set fld = .CreateField("MyTable", dbText, 64)
      .Fields.Append fld
      Set fld = .CreateField("MyField", dbText, 64)
      .Fields.Append fld
      Set fld = .CreateField("MyKey", dbText, 64)
      .Fields.Append fld
      Set fld = .CreateField("ChangedOn", dbDate)
      .Fields.Append fld
      Set fld = .CreateField("FieldName", dbText, 64)
      .Fields.Append fld
      Set fld = .CreateField("Field_OldValue", dbText, 255)
      .Fields.Append fld
      Set fld = .CreateField("Field_NewValue", dbText, 255)
      .Fields.Append fld
      Set fld = .CreateField("UserChanged", dbText, 128)
      .Fields.Append fld
      Set fld = .CreateField("CompChanged", dbText, 128)
      .Fields.Append fld
      Set fld = .CreateField("Action", dbtext, 64
      .Fields.Append fld
  End With
  db.TableDefs.Append tdf
  Set idx = Nothing
  Set fld = Nothing
  Set tdf = Nothing
  Set db = Nothing
  End Sub
  </code></pre>
  
  <p>After adding these objects, modify each form as follows:
  1. Set the Tag property for the form = the name of the underlying table.
  2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.
  3. Add the   </p>
  
  <pre><code>Form_BeforeUpdate event and invoke the tracking code using:
     TrackChanges Me
  </code></pre>
  
  <p>4. If you are using subforms, you'll need to perform these three steps for each subform as well.</p>
  
  <p>In my database I added a text field called Action to the tbl_ChangeTracker.  I need to know how to write the code to populate it.  Thanks in advance for any help I get.</p>