Solved Public Function Formula erroring on no data (1 Viewer)

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
I'm trying to find the right combination to get the code to not error when I hit a record that doesn't have text within it.

Error 2427, You entered an expression that has no value.

I've commented out all of my failures below the with txt.

Code:
Public Function SpellChecker(txt As TextBox) As Boolean
    On Error GoTo Err_SpellChecker
    
    With txt
        'If IsBlank(.value) Then 'nope
        'If Len(.value) = 0 Then 'nope
        'If nz(.Value),0) = 0 Then 'doesn't recognize as a legitimate formula
        'if len(.value & 0) = 0 then 'nope
        'If IsNull(Len(.value & "")) Then
        'If iserrror(Len(.value)) Then 'sub or function not defined error
            GoTo Exit_SpellChecker
        ElseIf Len(.value) > 0 Then
            DoCmd.SetWarnings False
            .SelStart = 1
            .SelLength = Len(.value)
            DoCmd.RunCommand acCmdSpelling
            .SelLength = 0
            DoCmd.SetWarnings True
        End If
    End With
Exit_SpellChecker:
    DoCmd.SetWarnings True
    Exit Function
Err_SpellChecker:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
    "Description: " & Err.Description & vbNewLine & vbNewLine & _
    "Function: SpellChecker" & vbNewLine & _
    IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
    "Module: basTest", , "Error: " & Err.Number

    Resume Exit_SpellChecker    
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,358
Hi. First thing to try is this:
Rich (BB code):
Public Function SpellChecker(txt As Variant) As Boolean
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
No change. Still errors if there is no text in the field.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:06
Joined
Oct 29, 2018
Messages
21,358
No change. Still errors if there is no text in the field.
Sorry, please disregard my earlier post. I misread your question. So, instead of using a With block. does it still error if you simply use something like?

If IsNull(txt) Then
 

Micron

AWF VIP
Local time
Today, 05:06
Joined
Oct 20, 2018
Messages
3,476
What event calls this? The control may not have a value at that point. In that case, you probably should be examining the Text property, not Value
Why not pass the text or value rather than the whole control?
 

bob fitz

AWF VIP
Local time
Today, 09:06
Joined
May 23, 2011
Messages
4,717
Might be helpful to know on which line the code breaks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:06
Joined
May 21, 2018
Messages
8,463
Code:
Public Function SpellChecker(txt As TextBox) As Boolean
    On Error GoTo Err_SpellChecker
    if trim(txt.value & " ") <> "" then
        'Handles null, emptystring, and space or spaces
        'Only way this fails if you have not prinatble characters only ex. VBCRLF
        DoCmd.SetWarnings False
        txt.setfocus
        .SelStart = 1
        .SelLength = Len(.value)
         DoCmd.RunCommand acCmdSpelling
         .SelLength = 0
         DoCmd.SetWarnings True
     End If
Exit_SpellChecker:
    DoCmd.SetWarnings True
    Exit Function
Err_SpellChecker:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
    "Description: " & Err.Description & vbNewLine & vbNewLine & _
    "Function: SpellChecker" & vbNewLine & _
    IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
    "Module: basTest", , "Error: " & Err.Number
    Resume Exit_SpellChecker   
End Function
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
@MajP I get an invalid or unqualified reference on the .SelStart line with your recommended code.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:06
Joined
May 21, 2018
Messages
8,463
Sorry. I took out the with. Just add in txt.Selectstart, txt.selectlength
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
@Micron
Code:
Private Sub JobTitle_Exit(Cancel As Integer)
    Call SpellChecker(Me.JobTitle)
End Sub

@MajP
Code compiles but still provides the 2467 error.
Code:
Public Function SpellChecker(txt As TextBox) As Boolean
    On Error GoTo Err_SpellChecker
    
    If Trim(txt.value & " ") <> "" Then
        'Handles null, emptystring, and space or spaces
        'Only way this fails if you have not prinatble characters only ex. VBCRLF
        DoCmd.SetWarnings False
        txt.SetFocus
        txt.SelStart = 1
        txt.SelLength = Len(txt.value)
        DoCmd.RunCommand acCmdSpelling
        txt.SelLength = 0
        DoCmd.SetWarnings True
    End If

Exit_SpellChecker:
    DoCmd.SetWarnings True
    Exit Function
    
Err_SpellChecker:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: SpellChecker" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basTest", , "Error: " & Err.Number
    Resume Exit_SpellChecker
    
End Function
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
Hold on. F8 is showing the error is happening after the end sub for the field. Need to troubleshoot further.

@bob fitz thanks for the reminder to relook at where it breaks.
 
Last edited:

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
There was likely nothing wrong with the starting code. I've removed a subform and the code isn't erroring.

I'm likely going to need help troubleshooting this form / subform combo. Working to build a demo database now.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:06
Joined
May 21, 2018
Messages
8,463
There was likely nothing wrong with the starting code. I've removed a subform and the code isn't erroring
This can happen especially if you have code that interacts with the subform. You can get into a condition where you cannot keep focus on the textbox even if you set it. I believe the selstart and sellength require the textbox to have focus.
txt.setfocus
.SelStart = 1
.SelLength = Len(.value)

The other issue that can be happening is that the value does not match the text. This obviously occurs if the form is in a dirty state. Imagine the value is "Dog" with length of three. Then you delete "dog" and the code runs in the before udpate. The len is three but there are no characters in the textbox because the text is "". Therefore, I would set focus always and check the text length not value length. = len(txt.text)
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
Gentlemen -
I've posted the reduced database to a previous posting on this issue, since it captures the troubleshooting of the vba code.

@MajP I haven't made the adjustments to the FATY observation you made yet. At this point, I think it's a completely different issue, not related to that.
 

dgreen

Member
Local time
Today, 04:06
Joined
Sep 30, 2018
Messages
397
I'm closing this one out. I got some helpful code suggestions for handling value changes during spell checking but the root cause of the issue doesn't appear to be spelling but some other poorly coded element of my database by your's truly. The previous posting points to where the conversation moved to.
 

Users who are viewing this thread

Top Bottom