Link to home
Start Free TrialLog in
Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America

asked on

2427 - You entered an expression that has no value Error

I am all of a sudden getting this error message on a form when I click the "Save" command button. I tested the code behind the button and the error seems to be coming from the "Me.Dirty = False" line of code. Why would I get the error on that line all of a sudden??

Here is the code behind my "Save" command button:

    If Me.Dirty And Form.NewRecord = False Then
        Me.txtAFModifiedTStamp = Now()                          'Sets value to current date/time
        Me.txtAFModifiedBy = Me.txtCreatedByUserName            'Sets value to CurrentUser Name
        'Me.Dirty = False
        MsgBox "Record has been saved!"
    End If

Open in new window

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Try with:

 If Me.Dirty And Me.NewRecord = False Then

/gustav
Avatar of Lawrence Salvucci

ASKER

Still get the error. It will save the record but the error still appears. If I take the entire Me.Dirty out of the code I get no error. So I know it's the Dirty part that is causing the error.
How about being explicit:

   If Me.Dirty = True And Me.NewRecord = False Then

and does Me.txtCreatedByUserName   have a value?

    Me.txtAFModifiedBy = "Lawrence"    

/gustav
I might try:

if Me.Dirty AND (me.NewRecord = False) Then

I like to wrap my conditions so I and anyone following me knows what I'm trying to do.  However, I cannot see why you would get that particular error on the

Me.Dirty = False

line.  Have you confirmed that there is a value in the txtCreatedByUserName textbox?  It may be that you are attempting to write a NULL or a zero length string to a field which requires a value.
Still get the error even with it being explicit. The Me.txtCreatedByUserName has a value. It's the windows username and populates the field AFModifiedBy in the table like a time stamp so we know which user made the last changes to that record.
Oops, guess I stepped on Gustav's post, that's what I get for walking away to get a cup of coffee.
@ Dale

Yes I confirmed that there is a value in the txtCreatedByUserName. But to eliminate that I took that line of code out and it still errors out on the Me.Dirty = False line. I just don't get how it all of a sudden started happening. Could the form be corrupt some how??
What event are you calling this from?

I would normally put this in the Form_BeforeUpdate event, in which case, you would not need to test for me.Dirty, because the Form_BeforeUpdate event does not fire unless the form is dirty.

Dale
I am calling this from my "Save" command button on the form. So I could put the Me.Dirty portion of the code in the Form_BeforeUpdate event and leave the rest of the code behind the Save command button?
my thought was to get rid of the me.Dirty portion of the If statement, actually not sure why you are only updating those values for a new record.  I usually just use something like:
Private Sub Form_BeforeUpdate(Cancel as Integer)

    If PassesChecks = False then      'this calls a function which checks for values in required fields
         Cancel = True
    Else
         Me.txtAFModifiedTStamp = Now()                          'Sets value to current date/time
         Me.txtAFModifiedBy = Me.txtCreatedByUserName            'Sets value to CurrentUser Name
         Me.Dirty = False
         MsgBox "Record has been saved!"
    End If

End Sub

Open in new window

I am updating those values whenever someone changes other data in other fields. Those are just the time stamp fields to show when the record was last updated.
What is PassesChecks? Never heard of that function
I would only update those fields when the entire record is written.  No need to do it when each item is changed, as they can always hit escape on those changes but doing so will not change the values you have pushed into those fields, which will keep the record Dirty, even if you Escaped out of all of the other changes to that record.
PassesChecks() is a function I create for every form that has specific required fields.  It does all of my error checking for required fields, compares start and end dates to make sure that they are in the right order, and those types of things.  

If it encounters an error, it pops up a message and returns a False to the BeforeUpdate event to prevent it from saving the record in its current state.
I found the source of the error after doing some extensive digging. I have code firing in the Form_Before_Update Event for my audit trail (see code below). This is where the 2427 error is coming from and I don't understand how this could all of a sudden change to start erroring out. I didn't change anything in this mod. What do you think would cause it from this code? Any ideas?

Before_Update Event Code:

Call Audit_Trail(Me, "RecordID", RecordID.Value)  'Writes any changes to record to tblAudit

Open in new window



Audit Trail Module Code:

Public Function Audit_Trail(MyForm As Form, UniqID_Field As String, UniqID As String)
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 ccnt As control
    Dim sUser As String
    
    Dim strSQL As String
    Const cQUOTE = """" 'Thats 2 quotes in sequence
        
    Dim Action, nullval As String
    nullval = "Null"
    sUser = Environ("UserName") 'get the users login name
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        Action = "*** New Record ***"
        'Broken down into 4 separate variables for ease of view and troubleshooting
        strSQL = "INSERT INTO dbo_tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action])"
        strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
        strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
        strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ";"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        Exit Function
    End If
    
    Dim changecnt As Integer
    changecnt = 0
    
    'Check each data entry control for change and record old value of the control.
    For Each ccnt In MyForm.Controls
    
    Select Case ccnt.ControlType
      Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ccnt.Name Like "*" & "test" & "*" Then GoTo TryNextCCNT   'Skip AuditTrail field.
        If (ccnt.Value <> ccnt.OldValue) Or _
           (IsNull(ccnt.Value) And Len(ccnt.OldValue) > 0 Or ccnt.Value = "" And Len(ccnt.OldValue) > 0) Then
          changecnt = changecnt + 1
        End If
    End Select
    
TryNextCCNT:
  Next ccnt
        
    If changecnt > 0 Then
      'gstrReason = InputBox("Reason for change(s)?", "Reason for change(s)?")
    End If
        
    '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 Like "*" & "test" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
        If ctl.Value <> ctl.OldValue Then
            Action = "*** Updated Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
            strSQL = "INSERT INTO dbo_tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
            strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
            strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
            strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
            strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            
        '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
            Action = "*** Added Info to Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO dbo_tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action])"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & nullval & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ";"
             
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
             
        '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
             Action = "*** Removed Info from Record ***"

            'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO dbo_tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & nullval & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
             
             DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
             DoCmd.SetWarnings True
         End If

    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If err.Number = 2001 Then 'You canceled the previous operation.
      'do nothing
    Else
        Beep
        MsgBox err.Number & " - " & err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function

Open in new window

Since you said the Save is actually saving the record, try this to see if it stops the error

On Error Resume Next
Me.Dirty = False
On Error GoTo 0  (or GoTo your Error Trap code)

BTW ... if you were to upgrade to at least A2010,  you can do Audit Trails with virtually no code :-)
My concern is that the audit trail isn't working when this error fires. So if I suppress it then I won't get the audit trail for those changes made. And it seems to only happen when updating an existing record. When you create a new record the error does not appear and the audit trail fires fine. One of these days I will upgrade to 2010. Not there yet... :)
that worked but the audit trail doesn't fire as I suspected. UGH. I hate problems like this on a Friday!
@ Dale ...
Per post above ... you can't put Me.Dirty = False in the Form BU event because it will create an endless loop ... since Me.Dirty = False (like behind a Save button) will trigger the Form BU event.

@ Lawrence ... what code is in the Form BU event now ?
Here is the code in the BU event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call Audit_Trail(Me, "RecordID", RecordID.Value)  'Writes any changes to record to tblAudit
End Sub

Open in new window

Why isn't that code in the After Update event ?
IE ... add the new record, then add to Audit Trail.
And if you are only doing this on a New Record (??) ... then why not use the After Insert event ?

Also ... does Audit_Trail() change any data in the Form's underlying Record Source ? (guessing not)
The audit trail instructions said to put that code in the BU event of the form. The Audit Trail code fires on new records and updated records. What I meant was that the audit trail is firing correctly when a new record is created and I do not get the error 2427. The audit trail doesn't work when I am just updating records which is the only time the 2427 error appears. No, the audit trail doesn't change any data. It just writes the old values and new values to the dbo_AuditTrail table.
"The audit trail instructions said to put that code in the BU event of the form"
Well, in no way would I agree with that (Microsoft KBs !!!!!!!!).
The intent of the Form BU event is to do validations at the Form level, check conditions, 'business rules',  etc.  Once whatever has been verified, and the record is saved (which is when AU or AI occurs) .... then you would do things like update an Audit Trail table and so on. Going out and doing things to 'external' tables (etc) during the BU event is problematic at best.

"The audit trail doesn't work when I am just updating records which is the only time the 2427 error appears."

Which part of the code is that ?
HA! I was younger and paid attention to everything MS said in their KB's. My bad! :)


This is the part of the Audit Trail code that fires when a record is just updated:

    If changecnt > 0 Then
      'gstrReason = InputBox("Reason for change(s)?", "Reason for change(s)?")
    End If
        
    '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 Like "*" & "test" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
        If ctl.Value <> ctl.OldValue Then
            Action = "*** Updated Record ***"
        
            'Broken down into 4 separate variables for ease of view and troubleshooting
            strSQL = "INSERT INTO dbo_tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
            strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
            strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
            strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
            strSQL = strSQL & ", " & cQUOTE & ctl.Value & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True

Open in new window

Joe, you are correct.  I should not have included the me.Dirty = False in the Form_BU event.

But you are wrong about the audit feature.  In order to test for ControlName.Value <> ControlName.OldValue, the record must still be dirty.  Once the record has been saved, the Value and OldValue properties will be the same.
"ControlName.OldValue, the record must still be dirty.  Once the record has been saved, the Value and OldValue properties will be the same."
Understood ... however ....
I would create a variable to hold those values ... to be used in the AU or AI event.
Again ... doing stuff like this in the BU event is very problematic ... as we are seeing here.
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
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
Agree to disagree regarding calling the audit log from within the Form_BU event.

strongly agree with Joe regarding the use of SetWarnings!  In this SQL statement you insert into a
date/time field and use the cQuote variable to wrap your date field, when you should be using a #
strSQL = "INSERT INTO dbo_tblAudit ([User], [DateTime], UniqID_Field, UniqID, Form, Field, _
                                    Prev_Value, New_Value, [Action], Reason) "
strSQL = strSQL & "SELECT " & cQUOTE & sUser & cQUOTE & ", #" & Now & "# , "
strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", "
strSQL = strSQL & cQUOTE & ctl.OldValue & cQUOTE & ", " & cQUOTE & ctl.Value & cQUOTE & ", " 
strSQL = strSQL & cQUOTE & Action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"

Open in new window

Thank you for all the help and ideas. The Audit Trail code was something I got from a MS KB article but I will make the changes you both mentioned. Maybe MS should revise this KB article...but I doubt they will. I will work on this a little later and hopefully eliminate that error. I will post back once I do.
"Maybe MS should revise this KB article"

Microsoft should revise 90 % of their KBs !!!  And they should be written by people who actually know something about the subject!
SOLUTION
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
SOLUTION
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
Wish I had your knowledge on how to set that all up. I'm still learning!