Track Changes in Form with sub-form & multiple tabs

[dirksza2009]

Hi,

I've made a multi user (4 end users) database in Access 2000. I've made data tables, reference tables etc which sits on a shared drive and I've made individual front ends for the end users which gives them specific views of the data.
I'd like to track all the changes made to a record which works find with the following code :
basAuditTrail:

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.
    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****nSQL 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

I call the above mentioned procedure in a single form with :
in event procedure : Before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, [Shipper ID])
End Sub

Now here's the problem, the above mentioned code seems to work OK when using a single form but as soon as I add in a SubForm (in which ideally I want to track changes made too) everything comes to a halt with the mention of :
"The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro."

Any help on this would be GREATLY appreciated!

Thanks

[ChipR]

Not enough information here, especially related to the subform. If you copied this BeforeUpdate code to the subform, it probably can't find a sub called AuditTrail because it's in another module. Side note, why is recordid a Control rather than a String?

[NeoPa]

I suspect this problem is related to something you've done (in the form setup somewhere) that you haven't actually told us about. Easy enough to do when you don't know what the problem is of course, but look at what's set up to link the code to the form.

As a separate point (unrelated to your problem), I would bring your attention to your use of cDQ within your SQL code. This should work, but doesn't produce very portable code (and it's actually not quite correct - even though Access itself still defaults to this way of quoting. See Quotes (') and Double-Quotes (") - Where and When to use them for a fuller explanation of the issues.

[dirksza2009]

Hi,
I've attached a watered-down version of the database (3 tables, 5 forms and 2 modules), as you open the database and try to update a field (in the OE_Small form) and click in the sub-form the error msg appears....
I've tried a work-a-round by using 2 modules (so that the main form and subform can call on a module each) but it didn't work either!
I'm pretty much at my wits end! Any help would be appreciated!
Thanks