Solved

2427 - You entered an expression that has no value Error

Posted on 2016-10-14
33
29 Views
Last Modified: 2016-10-29
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

0
Comment
Question by:Lawrence Salvucci
  • 14
  • 9
  • 7
  • +2
33 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Try with:

 If Me.Dirty And Me.NewRecord = False Then

/gustav
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
How about being explicit:

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

and does Me.txtCreatedByUserName   have a value?

    Me.txtAFModifiedBy = "Lawrence"    

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Oops, guess I stepped on Gustav's post, that's what I get for walking away to get a cup of coffee.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
@ 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??
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
What is PassesChecks? Never heard of that function
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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 :-)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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... :)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
that worked but the audit trail doesn't fire as I suspected. UGH. I hate problems like this on a Friday!
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
@ 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 ?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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 ?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
"This is the part of the Audit Trail code that fires when a record is just updated:"
I see. Well ... that is a LOT of stuff to be happening in the BU event ...
My suggestion is ....
Create variables to hold values that may be changing (set in BU event, etc).
Then move all the Audit Trail call to the AfterUpdate event.  As you can see, it's EXTREMELY difficult to troubleshoot stuff in complex BU events.


As a side note .... re

            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True

Let me suggest never using SetWarnings. If the strSQL (and action query) fails for ANY reason ... you will never know - since error will not be exposed to UI.
Instead ... replace with

    CurrentDB.Execute strSQL, dbFailOnError
Then add Error Trapping code.  This way. you will not get the annoying message prompts ... and more importantly if an error occurs (that you DO want to know about), your error trapping code will catch it.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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!
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
Comment Utility
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.
Not if you are only populating these fields in your Save button you're not.  The Save button should only check for dirty and then save the record.  It should not include any validation or population of logging fields.  All that code belongs in the form's BeforeUpdate event where it will occur REGARDLESS of what caused a record to be saved.

As to logging code in general - if you put the code into the BeforeUpdate event, you will be creating log records even when a record did not get saved so I would not use that event.  I would use the AfterUpdate event and that means that you can't use code that compares the .value property with the .oldValue property since in the AfterUpdate event, they would both be the same.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
Comment Utility
"you will be creating log records even when a record did not get saved so I would not use that event."

The way I usually do it is to run my PassesChecks function to first check whether the data passes the tests for required fields, valid values, etc.  If not, then I exit the BU event before any of the other code executes.

Then, I run my Audit Trail code and write all of the changes to the audit trail table, returning the date/time stamp of the records just written.  Note: I don't use Now() in the SQL string, I set a date/time variable at the top of the procedure so that all of the records have the same date/time stamp.  The return argument of the AuditTrail is the Date/Time value mentioned above.

If you are worried about the BU raising an error, and not actually updating the original record, you can delete the records written to the AuditLog in the BU events error handler.

Been doing it this way for years.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Wish I had your knowledge on how to set that all up. I'm still learning!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

7 Experts available now in Live!

Get 1:1 Help Now