function - can a string be an optional variable?

[krowe]

I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.

I have researched about putting optional variables in, but I read this only works with the type VARIANT.

Is there a way to make a string optional as my fields contain text?

here is my code so far;

Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID 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
                            
                            If IsMissing(Screen.ActiveForm.Controls(UserID).Value) = True Then
                                ![User] = "NA"
                            Else
                                ![User] = Screen.ActiveForm.Controls(UserID).Value
                            End If
                            
                            If IsMissing(Screen.ActiveForm.Controls(SimID).Value) = True Then
                                ![Sim] = "NA"
                            Else
                                ![Sim] = Screen.ActiveForm.Controls(SimID).Value
                            End If
                            
                            If IsMissing(Screen.ActiveForm.Controls(DeviceID).Value) = True Then
                                ![Sim] = "NA"
                            Else
                                ![Device] = Screen.ActiveForm.Controls(DeviceID).Value
                            End If
                            
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
            
        Case "New"
            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
                            If IsMissing(Screen.ActiveForm.Controls(UserID).Value) = True Then
                                ![User] = "NA"
                            Else
                                ![User] = Screen.ActiveForm.Controls(UserID).Value
                            End If
                            
                            If IsMissing(Screen.ActiveForm.Controls(SimID).Value) = True Then
                                ![Sim] = "NA"
                            Else
                                ![Sim] = Screen.ActiveForm.Controls(SimID).Value
                            End If
                            
                            If IsMissing(Screen.ActiveForm.Controls(DeviceID).Value) = True Then
                                ![Sim] = "NA"
                            Else
                                ![Device] = Screen.ActiveForm.Controls(DeviceID).Value
                            End If
                            ![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
                If IsMissing(Screen.ActiveForm.Controls(UserID).Value) = True Then
                        ![User] = "NA"
                Else
                        ![User] = Screen.ActiveForm.Controls(UserID).Value
                End If
                            
                If IsMissing(Screen.ActiveForm.Controls(SimID).Value) = True Then
                        ![Sim] = "NA"
                Else
                        ![Sim] = Screen.ActiveForm.Controls(SimID).Value
                End If
                            
                If IsMissing(Screen.ActiveForm.Controls(DeviceID).Value) = True Then
                        ![Sim] = "NA"
                Else
                        ![Device] = Screen.ActiveForm.Controls(DeviceID).Value
                End If
                .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

Thanks

Kev

 

[spikepl]

Do not trust incorrect interpretations of what you read.

 

[krowe]

Ok, must be something else wrong.

The code doesn't run, it fails when I get to the first ismissing bit, so I read a bit and thought that may be the issue.

When it gets to that line I get the error cannot find field " referred to in your expression... I know the field doesn't exist, and I hoped the code would handle this.

Do I have to do anything when I call the function, this is the code I use in the form without the extra fields:

If Me.NewRecord Then
        Call AuditChanges("DeviceID", "NEW")
    Else
        Call AuditChanges("DeviceID", "EDIT")
    End If
 End Sub

it works when I call it from a form with all the fields using this:

If Me.NewRecord Then
        Call AuditChanges("JoinID", "NEW", "UserID", "DeviceID", "SimID")
    Else
        Call AuditChanges("JoinID", "EDIT", "UserID", "DeviceID", "SimID")
    End If
 End Sub

I tried:

If Me.NewRecord Then
        Call AuditChanges("DeviceID", "NEW", , ,)
    Else
        Call AuditChanges("DeviceID", "EDIT", , , )
    End If
 End Sub

but it wont compile

Thanks for your help

Kev

 

[vbaInet]

I don't know about the source of the code you found but the following link seems to be popular:

http://www.access-programmers.co.uk/forums/showthread.php?t=44231

 

[vbaInet]

I have researched about putting optional variables in, but I read this only works with the type VARIANT.

You can have any other type (String, Long etc) as optional variables the only difference is when it comes to using IsMissing() on the optional variable. IsMissing() will only work for Variants but besides that any other type can be optional.

 

[Galaxiom]

IsMissing() is used to test for the presence of arguments supplied in subs and functions. It is not intended to test for values in objects.

You don't even appear to be using the sub's declared arguments inside the sub.

Moreover, the way you are constructing the sub is missing the point. It would be more usual to pass the controls or control values as arguments rather than hard code referencing them inside the sub.