Another ByRef type mismatch

[Levonas]

I have tried to reference other posts to fix my issue but the ByRef that another user posted with ECN# and fixed their issue, didn't really help me but I am using darn near the same coding for the same reason, with close to the same issue but his was due to an illegal character.
I left my one main table's autonumber as ID and trying to reference it causes error.
So I start with this;

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.
    Select Case ctl.ControlType
        Case acTextBox, acCheckBox, acComboBox
       If (.Value.OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) 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 & CurrentUser() & 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 Select
    End With
  Next
  Set ctl = Nothing
  Exit Sub
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

Then I call it like this in my form;

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

Every time it does a ByRef error and it highlights "Call AuditTrail(Me, ID)" when I go to debug.
Any thoughts?

[June7]

You have declared object variables for form and control in the AuditTrail procedure declaration but nowhere are these object variables Set.

I have never tried passing form and control objects by reference in procedure call. Did you adapt this code? Can you provide the source?

[Levonas]

Sure, here is the link to where I am trying to work this from;

http://www.techrepublic.com/article/...o-access-data/

I barely adapted this. Only off of the comments below the post on that site where others adapted lines of code to work better.
I named the audit table "Audit" just like it was said to do. The table it is auditing is called "MASTER" and it's autonumber is called ID since I never changed the default.
The audit table is set up exactly like the one on the site as well.
I am baffled as to why it works for some but I get it to work with that one error every time.

[June7]

I think that code is a generic procedure that can be called by any form and uses only one table for the log. Nice if it works. So I tested.

The procedure call should pass form and control objects but the control argument fails. Referencing the control by its name is just passing the ID value that is in the control. This is revealed in step debugging. Putting control name in quotes doesn't help. Nor does Me.Controls("ID").

This article says no need to pass the form object http://www.vbforums.com/showthread.p...ub-or-function

Sorry, I don't know how to resolve.

WAIT!! I just realized I was using the field name, not the control name. Now I get a different error "Operation is not supported for this type of object." and no record created in the Audit table. I comment out the On Error line and the debugger hits: If .Value <> .OldValue Then. Doesn't like the .OldValue property.

Okay, I give up.

Another approach here http://allenbrowne.com/AppAudit.html