Solved

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

Posted on 2004-07-31
3
1,679 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:chthomas
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
walterecook earned 125 total points
ID: 11687690
I can't reproduce that error specifically but I can make it bomb out because of nulls.  Perhaps you should evaluate the special case of a frame being null, separately:
if ctl.controltype = acoptiongroup and isnull(ctl.value) then
   ' it's a frame and its null
' do whatever you want to do about it
else ' do your normal thing
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 if
0
 

Author Comment

by:chthomas
ID: 11687840
I will get back with my comments. Am fiddling around the code.
0
 

Author Comment

by:chthomas
ID: 11691234
O.K. Problem is solved. I altered the code a little and seems to be working fine. Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now