VBA Audit Trail code throwing Argument Not Optional error

Question

I have built one database where the below audit trail code works flawlessly for both forms and sub-forms in Access 2010. But now that I am using it again in another database, I now get an error "Argument Not Optional" at the first Call. Why would this work in one database and not the other if they both have had the sub-form created the same exact way? I can not get the database to give me more information outside of the not so helpful error code. My best guess is that it has something to do with Sub TrainingEntryAuditChanges(IDField As String, UserAction As String, FormToAudit As Form) but I can't really tell. Like I said, it works in one database, but not this one for some reason. Any ideas?

Module Code:

***ABOVE CODE OMITTED INTENTIONALLY***
'Audit module code for employee training entry form's sub form
Sub TrainingEntryAuditChanges(IDField As String, UserAction As String, FormToAudit As Form)
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)

'Get computer IP address
Dim myWMI As Object, myobj As Object, itm
Set myWMI = GetObject("winmgmts:\\.\root\cimv2")
Set myobj = myWMI.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each itm In myobj
        getMyIP = itm.IPAddress(0)
    Next

'If user is editing an existing record:
Select Case UserAction
    Case "EDIT"
        For Each ctl In FormToAudit
            If ctl.Tag = "Audit" Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                    With rst
                        .AddNew
                        ![DateTime] = datTimeCheck
                        ![UserName] = strUserID
                        ![UserComputer] = getMyIP
                        ![FormName] = FormToAudit.Name
                        ![Action] = UserAction
                        ![RecordID] = FormToAudit.Controls(IDField).Value
                        ![FieldName] = ctl.ControlSource
                        ![OldValue] = ctl.OldValue
                        ![NewValue] = ctl.Value
                        .Update
                    End With
                End If
            End If
        Next ctl

    'If a user is creating a new record:
    Case Else
        With rst
            .AddNew
            ![DateTime] = datTimeCheck
            ![UserName] = strUserID
            ![UserComputer] = getMyIP
            ![FormName] = FormToAudit.Name
            ![Action] = UserAction
            ![RecordID] = FormToAudit.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

'If error then:
AuditChanges_Err:
Dim strError As String
Dim lngError As Long
Dim intErl As Integer
Dim strMsg As String
strError = Err.Description
lngError = Err.Number
intErl = Erl
strMsg = "Line : " & intErl & vbCrLf & _
       "Error : (" & lngError & ")" & strError
MsgBox strMsg, vbCritical
Resume AuditChanges_Exit

End Sub

Before_Update code on subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call TrainingEntryAuditChanges("ID", "NEW") ***ERROR THROWN HERE***
Else
    Call TrainingEntryAuditChanges("ID", "EDIT")
End If
End Sub

tospig tospig · Accepted Answer · 2015-01-23T00:18:51

The Argument Not Optional is thrown when you are calling a routine with the incorrect number of arguments required for that routine.

In your code

Sub TrainingEntryAuditChanges(IDField As String, UserAction As String, FormToAudit As Form)

requires three arguments, IDField, UserAction, and FormToAudit.

However, in your Call

Call TrainingEntryAuditChanges("ID", "NEW") ***ERROR THROWN HERE***

you are only passing it two arguments: ID, NEW. You need to pass it a third argument (which looks like it will be the form). Try using me as the third argument to pass the 'current' form that is being updated and therefore calling the routine.

VBA Audit Trail code throwing Argument Not Optional error

1 Answers