Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

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

0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 14
  • 9
  • 7
  • +2
3 Solutions
 
Gustav BrockCIOCommented:
Try with:

 If Me.Dirty And Me.NewRecord = False Then

/gustav
0
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dale FyeCommented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Dale FyeCommented:
Oops, guess I stepped on Gustav's post, that's what I get for walking away to get a cup of coffee.
0
 
Lawrence SalvucciSystems ManagerAuthor Commented:
@ 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
 
Dale FyeCommented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Dale FyeCommented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
What is PassesChecks? Never heard of that function
0
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
that worked but the audit trail doesn't fire as I suspected. UGH. I hate problems like this on a Friday!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@ 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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
Dale FyeCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Dale FyeCommented:
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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
PatHartmanCommented:
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
 
Dale FyeCommented:
"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
 
Lawrence SalvucciSystems ManagerAuthor Commented:
Wish I had your knowledge on how to set that all up. I'm still learning!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 14
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now