Link to home
Start Free TrialLog in
Avatar of chthomas
chthomasFlag for United Arab Emirates

asked on

Audit Trial and "Error - 2427 You entered an expression that has no value".

Am trying to do an Audit of changes made to my database. Downloaded the modified microsoft audit code (given below) from the net. Am calling the Audit Trial code on "Before update event" of the form. My audit works fine except the following problem.

I have got an option group with values ranging from 1 to 3 on my form.There is no default value set for the option group. My problem, if this option group is present in the form, then am getting the error message "Error - 2427 You entered an expression that has no value".

Am getting the error at the following place in the code

If ctl.Value <> ctl.OldValue Then

I tried setting a default value for the option group still the same error. There are approximately 40 other various controls on my form and the audit works fine with them except this option group. I tried going through the code, but couldn't figure out what's going wrong.

Any ideas or suggestions?

Regards,

charley



Public Function Audit_Trail()
On Error GoTo Err_Audit_Trail
   
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
   
    Dim MyForm As Form
    Dim ctl As Control
    Dim sUser As String
    Set MyForm = Screen.ActiveForm
'    sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
    sUser = CurrentUser
   
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
   
    'Set date and current user if the form (current record) has been modified.
    MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
   
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
   
    'Only check data entry type controls.
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
            'If old value is Null and new value is not Null
            ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
            'If new value is Null and old value is not Null
            ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
            End If
    End Select
   
TryNextControl:
    Next ctl
   
Exit_Audit_Trail:
    Exit Function
   
Err_Audit_Trail:
    If Err.Number = 64535 Then 'Operation is not supported for this type of object.
        Exit Function
    ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
        Beep
        MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
   
End Function
ASKER CERTIFIED SOLUTION
Avatar of walterecook
walterecook
Flag of United States of America image

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
Avatar of chthomas

ASKER

I will get back with my comments. Am fiddling around the code.
O.K. Problem is solved. I altered the code a little and seems to be working fine. Thanks.