title: Access 2010 Audit Trail on SubForms
body: <p>I am having trouble getting the code I found for an audit trail to work with sub forms. The origninal code is from <a href="http://www.fontstuff.com/access/acctut21.htm" rel="nofollow">http://www.fontstuff.com/access/acctut21.htm</a>. I would rather stick to this code than using Allen Browne's code <a href="http://allenbrowne.com/appaudit.html" rel="nofollow">http://allenbrowne.com/appaudit.html</a>. It seems to be a problem with <code>Screen.ActiveForm.Controls</code>. I have read that this does not work with sub forms. Is there a way I can alter this to audit a sub form in my database?</p>
  
  <p>When I record the data in the sub form, I get the following error: Microsoft can't find the field "CalSubID" referred to in your expression."</p>
  
  <p>In a module I have this code (this is just part of it that I think is having issues):</p>
  
  <pre><code>Sub AuditChanges(IDField As String, UserAction As String)
  On Error GoTo AuditChanges_Err
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim ctl As Control
  Dim datTimeCheck As Date
  Dim strUserID As String
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  datTimeCheck = Now()
  strUserID = Forms!Login!cboUser.Column(1)
  Select Case UserAction
      Case "EDIT"
          For Each ctl In Screen.ActiveForm.Controls
              If ctl.Tag = "Audit" Then
                  If Nz(ctl.Value) &lt;&gt; Nz(ctl.OldValue) Then
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = Screen.ActiveForm.Name
                          ![Action] = UserAction
                          ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                          ![FieldName] = ctl.ControlSource
                          ![OldValue] = ctl.OldValue
                          ![NewValue] = ctl.Value
                          .Update
                      End With
                  End If
              End If
          Next ctl
      Case Else
          With rst
              .AddNew
              ![DateTime] = datTimeCheck
              ![UserName] = strUserID
              ![FormName] = Screen.ActiveForm.Name
              ![Action] = UserAction
              ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
              .Update
          End With
  End Select
  AuditChanges_Exit:
  On Error Resume Next
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing
  Exit Sub
  AuditChanges_Err:
  MsgBox Err.Description, vbCritical, "ERROR!"
  Resume AuditChanges_Exit
  End Sub
  </code></pre>
  
  <p>Then in my "before update" and "AfterDelConfirm" events for the subform I have (where "CalSubID" is the PK for the subform and this is what the main module code uses to track the changes):</p>
  
  <pre><code>-----------------------------------------------------------------------
  Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.NewRecord Then
      Call AuditChanges("CalSubID", "NEW")
  Else
      Call AuditChanges("CalSubID", "EDIT")
  End If
  End Sub
  -----------------------------------------------------------------------
  Private Sub Form_AfterDelConfirm(Status As Integer)
  If Status = acDeleteOK Then Call AuditChanges("CalSubID", "DELETE")
  End Sub
  -----------------------------------------------------------------------
  </code></pre>
  
  <p>Modified Code:</p>
  
  <pre><code>Sub AuditChanges(IDField As String, UserAction As String)
  On Error GoTo AuditChanges_Err
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim ctl As Control
  Dim datTimeCheck As Date
  Dim strUserID As String
  
  'added code
  Dim SubFormName As String
  
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  datTimeCheck = Now()
  strUserID = Forms!Login!cboUser.Column(1)
  
  'msgbox to display name (just for now to test code)
  MsgBox (" " &amp; Screen.ActiveForm.Name &amp; " ")
  
  'IF THEN statement to check if user is using form with subform
  If Screen.ActiveForm.Name = "Cal Form" Then
  SubFormName = "Cal Form Sub"
  
      Select Case UserAction
      Case "EDIT"
          For Each ctl In Screen.ActiveForm
              If ctl.ControlType = acSubform Then
              SubFormName = ctl.Name
              If ctl.Tag = "Audit" Then
                  If Nz(ctl.Value) &lt;&gt; Nz(ctl.OldValue) Then
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = SubFormName
                          ![Action] = UserAction
                          ![RecordID] = Forms![Screen.ActiveForm.Name]![SubFormName].Form![IDField].Value
                          ![FieldName] = ctl.ControlSource
                          ![OldValue] = ctl.OldValue
                          ![NewValue] = ctl.Value
                          .Update
                      End With
                  End If
              End If
  'Getting error message at the --Next ctl-- line below, "next without for" message....
          Next ctl
      Case Else
          With rst
              .AddNew
              ![DateTime] = datTimeCheck
              ![UserName] = strUserID
              ![FormName] = SubFormName
              ![Action] = UserAction
              ![RecordID] = Forms![Screen.ActiveForm.Name]![SubFormName].Form![IDField].Value
              .Update
          End With
          Set ctl = Nothing
  End Select
  
  Else
  
  Select Case UserAction
      Case "EDIT"
          For Each ctl In Screen.ActiveForm.Controls
              If ctl.Tag = "Audit" Then
                  If Nz(ctl.Value) &lt;&gt; Nz(ctl.OldValue) Then
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = Screen.ActiveForm.Name
                          ![Action] = UserAction
                          ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                          ![FieldName] = ctl.ControlSource
                          ![OldValue] = ctl.OldValue
                          ![NewValue] = ctl.Value
                          .Update
                      End With
                  End If
              End If
          Next ctl
      Case Else
          With rst
              .AddNew
              ![DateTime] = datTimeCheck
              ![UserName] = strUserID
              ![FormName] = Screen.ActiveForm.Name
              ![Action] = UserAction
              ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
              .Update
          End With
  End Select
  
  
  AuditChanges_Exit:
  On Error Resume Next
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing
  Exit Sub
  AuditChanges_Err:
  MsgBox Err.Description, vbCritical, "ERROR!"
  Resume AuditChanges_Exit
  End Sub
  </code></pre>