CODEWITHSUNDEEP
vbams-access
LenC
LenC

Reputation: 5

How to fix '438 - Object Doesn't Support Property or Method Error'

I have included an audit trail code to be called to 2 different forms in my access database. The code works fine for one of the forms but in the other form it produces a 438 error.

-The same parameter is used to call the code in both forms -The debugger highlights this line : 'If Nz(ctl.Value) <> Nz(ctl.OldValue) Then -I have attempted to comment out the code which calls the procedure and the problem appears to be with the parameter "SingleName" -I have checked both the Control Source and Name for the textbox and both appear correct.

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 TBL_AuditTrail", 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.Number & Err.Description
    Resume AuditChanges_Exit
End Sub





Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
        Call AuditChanges("SingleName", "NEW")
    Else
        Call AuditChanges("SingleName", "EDIT")
    End If
End Sub

The BeforeUpdate event of the form is supposed to call the procedure and send any changes, deletions or additions to TBL_AuditTrail. After the data is inputted and I attempt to save, the 438 error occurs. The information is still sent to the table (TBL_AuditTrail)

Upvotes: 0

Views: 516

Answers (2)

Gustav
Gustav

Reputation: 55841

An unbound control doesn't have an OldValue property. You could check for that:

If ctl.ControlSource <> "" Then
    ![OldValue].Value = ctl.OldValue
Else
    ' Skip unbound control.
End If

Upvotes: 1

Karlomanio
Karlomanio

Reputation: 371

Without seeing the three forms in question, I can only say that something is different on Screen.ActiveForm.Controls(IDField) field. I would compare the properties of all three fields to see how the one that is failing is different.

Upvotes: 0

Related Questions