[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-07-31
3
Medium Priority
?
1,707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
walterecook earned 375 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

656 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