Argument not optional.

[Homegrownandy]

I'm using this code to try and create an audit trail on my database.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not Me.NewRecord Then Call AuditChanges("Order_ID")
End Sub

This refers to:

Option Compare Database
Option Explicit
'
' ================================================
' Code by Martin Green Email: martin@fontstuff.com
' Visit my Office Tips website @ www.fontstuff.com
' YouTube tutorials www.youtube.com/martingreenvba
' ================================================
'
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 = Environ("USERNAME")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> 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

But I get the error Argument not optional on the first code. Is it obvious to anyone why that is? This seems to work in the example and I cant see a difference.

[Missinglinq]

The Sub calls for two arguments

Sub AuditChanges(IDField As String, UserAction As String)

and you're only providing one

Call AuditChanges("Order_ID")

Linq ;0)>

[Homegrownandy]

Hi, In the example on the website, its as I have written in parts. But I appreciate you explaining why this didn't work. There are other parts on the example I may use now I know why.

Understanding the error is more important than fixing it. so thanks again.

Richmond sausages and Yorkshire tea are the best.

[orange]

Andy,

I have recently worked with a poster re Martin Green's Audit routine. There are issues with Deletes in his code.
The thread and a sample database with revamped Audit processes that works with Forms and Subforms and handles Deletes is located here.

[Homegrownandy]

This will take me some time to implement I think, but it looks simple enough. That's great, thanks for that.