2427 - You Entered an expression that has no value
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.
Scott McDaniel (EE MVE )
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.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok when I do that the minute I open the form I get that error message. What does tell you?
ASKER
Forgot to answer your question....No there is no reference in any row source on the form.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
This is the code in the module:
This is the code that fires in the BeforeUpdate event:
Call Audit_Trail(Me, "RecordID", RecordID.Value) 'Writes any changes to record to tblAuditThis 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