Solved

2427 - You Entered an expression that has no value

Posted on 2014-09-12
7
82 Views
Last Modified: 2015-03-14
I have 6 option groups on my form and every time I try to update another control on my form I get the "2427 - You entered an expression that has no value" error. I tracked it down to the 6 option groups on my form. When I remove those from my form I no longer get the error. When I say I get this when I update another control on my form I mean after I update that control and recalc the form is when I get the error. Why would these cause that error message? I'm not sure I understand why this happens only with option groups.
0
Comment
Question by:Lawrence Salvucci
  • 4
  • 2
7 Comments
 
LVL 84
Comment Utility
Are you referring to those Option Groups somewhere in code? If so, and if there is no value set for the group, then you'll get that error.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
No, that's the thing. There is no reference to them anywhere in my code. Do you mean that there should be a default value set regardless if there is a reference in my code or not?
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
Comment Utility
Could there be a reference to one of these option groups in the row source of a combo box or listbox?  

Another thing to try:  set a value for each option group in the form Load event, and see if that eliminates the problem.  (This can be done for unbound option groups.)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
ok when I do that the minute I open the form I get that error message. What does tell you?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Forgot to answer your question....No there is no reference in any row source on the form.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
Are you running any code in the Open or Load or Current event of the Form? If so, set a breakpoint in each, and then open the form and step through the code to determine where it errors.

Also, if you have any Subforms, set breakpoints in the same events of those forms and run it.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Ok I found the problem. I have an AuditTrail that keeps track of all changes on my form. The code is below. The code fires from the BeforeUpdate event of the form. I can see that Option Groups are part of the code. Any ideas why it would error out because I have OptionGroups on my form?

This is the code that fires in the BeforeUpdate event:

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

Open in new window



This is the code in the module:

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 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 "*" & "txt" & "*" 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 "*" & "txt" & "*" 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 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 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 to Record ***"

            'Broken down into 4 separate variables for ease of view and troubleshooting
             strSQL = "INSERT INTO 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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

11 Experts available now in Live!

Get 1:1 Help Now