Link to home
Start Free TrialLog in
Avatar of qvfps
qvfps

asked on

Runtime Error 2427 on Audit Trail Function

I am using the following function to track changes to a database.  It was working fine until I changed the data property AllowAdditions to false on a subform.  The subform is tied to a combo box and is linked to the main form based on the check box.   If I select a value in the check box before an event triggers the audit trail it works fine.  If I have not accessed the subform then I generate a Run-time Error 2427: You entered an expression that has no value.  The error is on the line where I check to see if there is a previous value: If IsNull(frmCtrl2.oldValue) Or frmCtrl2.oldValue = "" Then

I changed the AllowAdditions property to false because if the property is set to true and you move the wheel on the mouse it takes you to a blank record on the form.   This causes problems with the database and it has confused several users.  

The On Error function does not trap this error.  Is there a way to stop the mouse from moving you to a new record, trap the error so I can skip to the next field or check to see if the property is set?

I am using Microsoft Access 2000 on WIndows 2000.

Thanks for any suggestions

Function AuditTrail(WID As Long, OID As Long, myID As Long)
On Error GoTo Err_AuditTrail
   
    Dim MyForm As Form
    Dim frmCtrl, frmCtrl2 As Control
    Dim myName As String
   
    Set MyForm = Screen.ActiveForm
    myName = MyForm.Name
   
    'Check each data entry control for change and record
    'old value of Control.
    For Each frmCtrl In MyForm.Controls
    'Only check data entry type controls.
    Select Case frmCtrl.ControlType
        Case 112
            For Each frmCtrl2 In frmCtrl.Controls
                If frmCtrl2.Name <> "Updates" Then
                    Select Case frmCtrl2.ControlType
                        Case acTextBox, acComboBox, acListBox, acCheckBox
                        ' If control was previously Null, record "previous  value was blank."
                            'MsgBox ("frmCtrl2.Name = " & frmCtrl2.Name)
'THE FOLLOWING LINE GENERATES THE ERROR IF THE ALLOWADDITIONS PROPERTY OF THE FORM IS FALSE
                            If IsNull(frmCtrl2.oldValue) Or frmCtrl2.oldValue = "" Then
                                Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
                            ' If control had previous value, record previous value.
                            ElseIf IIf(IsNull(frmCtrl2.Value), "", frmCtrl2.Value) <> frmCtrl2.oldValue Then
                                Call addAuditTrail(myName, frmCtrl2.Name, frmCtrl2.oldValue, frmCtrl2.Value, WID, OID, myID)
                            End If
                    End Select
                End If
            Next frmCtrl2
        Case acTextBox, acComboBox, acListBox, acCheckBox
            If frmCtrl.Name <> "Updates" And frmCtrl.Enabled Then
            ' If control was previously Null, record "previous value was blank."
                If IsNull(frmCtrl.oldValue) Or frmCtrl.oldValue = "" Then
                    Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
                ' If control had previous value, record previous value.
                ElseIf IIf(IsNull(frmCtrl.Value), "", frmCtrl.Value) <> frmCtrl.oldValue Then
                    Call addAuditTrail(myName, frmCtrl.Name, frmCtrl.oldValue, frmCtrl.Value, WID, OID, myID)
                End If
            End If
        End Select
    Next frmCtrl

Exit_AuditTrail:
    Exit Function
     
Err_AuditTrail:
    If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
    End If
    Resume Exit_AuditTrail
End Function
Avatar of aesmike
aesmike

Check out Lebans website.  He has a good Mouse Wheel solution.
ASKER CERTIFIED SOLUTION
Avatar of aesmike
aesmike

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial