Solved Spell Check On Null Values Issue (1 Viewer)

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
When I delete all of the data within a field that had values, my vba code is giving me errors (invalid use of null). I've modified it as you see below. Does this make sense or would there be a better way to handle this situation?

Previous code
Code:
Private Sub Purpose_Agenda_AfterUpdate()
'http://eileenslounge.com/viewtopic.php?f=29&t=10183
'Skips warning message if no spelling error
    If Not IsNull(Me.Purpose_Agenda) Then
        With Me.Purpose_Agenda
            .SetFocus
            .SelStart = 0
            .SelLength = Len(Me.Purpose_Agenda) 'Errors here.
        End With
        DoCmd.SetWarnings False
        RunCommand acCmdSpelling
        DoCmd.SetWarnings True
    End If
End Sub

Changed to:
Code:
            .SelLength = Len(Nz((Me.Purpose_Agenda), 0)) ' I've added the Nz to the .SelLength portion.  Modified from the original code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
Hi. You're already checking for Null, so I'm not sure using Nz() is necessary. I guess it wouldn't hurt either.
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
Then why would the code run? Why would it error out?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:00
Joined
May 21, 2018
Messages
8,463
I cannot see an error, not sure why that fails. What about?
.SelLength = Len(Me.Purpose_Agenda & "")
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
It stops on the line below with an error (invalid use of null) telling me I have a null value.

.SelLength = Len(Me.Purpose_Agenda)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
It stops on the line below with an error (invalid use of null) telling me I have a null value.

.SelLength = Len(Me.Purpose_Agenda)
But if you were stepping through it, you will have to ask yourself, how did it get past the Not IsNull() check? What is the current value in Me.Purpose_Agenda?
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
The value was blank when the code ran. I had a value in the field originally but then deleted that information and then the vba ran.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
The value was blank when the code ran. I had a value in the field originally but then deleted that information and then the vba ran.
Hi. Blank could mean a couple of things. When you step through the code, does it make sense to you when you see what is happening to the code line by line? If the value is a "blank," is it Null or a ZLS? If the IsNull() line is executed, can you verify what the value is then?
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
In a meeting and I need to stop multi-tasking.... Will respond in a bit.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
In a meeting and I need to stop multi-tasking.... Will respond in a bit.
Understood. If you need help troubleshooting it, please consider posting a demo version of your db. Cheers!
 

Micron

AWF VIP
Local time
Today, 04:00
Joined
Oct 20, 2018
Messages
3,476
Maybe we should ask how you are doing the deletion. By keyboard or code?
You could check for zls by placing this before the If IsNull
If Me.Purpose_Agenda = "" then msgbox "is empty string"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2013
Messages
16,553
Just a thought - to set the selLength property, the control must have the focus - your code is in the afterupdate event which occurs before the lostfocus event so it already has the focus. Suggest comment out your setFocus line to see if that makes a difference - My thinking is that the isnull line is allowing through a zls and the setfocus 'resets' it to null

cure might be

If nz(Me.Purpose_Agenda,"")<>"" then
With Me.Purpose_Agenda
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
Visual of issue from a VBA perspective (attached). This shows that the code knows that Me.Purpose_Agenda is null at the very beginning and for some reason doesn't stop the spell check. At the arrow you see the value showing up on hover.

I'm using the keyboard to remove the text from the field, if that matters to the discussion.

Still trying out other thoughts you all have shared.
 

Attachments

  • Picture1.png
    Picture1.png
    305.1 KB · Views: 193

theDBguy

I’m here to help
Staff member
Local time
Today, 01:00
Joined
Oct 29, 2018
Messages
21,358
Visual of issue from a VBA perspective (attached). This shows that the code knows that Me.Purpose_Agenda is null at the very beginning and for some reason doesn't stop the spell check. At the arrow you see the value showing up on hover.

I'm using the keyboard to remove the text from the field, if that matters to the discussion.

Still trying out other thoughts you all have shared.
Hi. I'd like to see the If IsNull() line highlighted and then hover over the control name. Then hit F8 and show us which line gets highlighted next.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2013
Messages
16,553
click on the grey bar to the left on the 'isnull' line, a maroon dot will appear. This will stop the code on that line when you next run it so you can then hover over me.purpose_agenda to see the value. Then hit the F8 button to advance one line.

did you see post #13?
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
Visual of next step in code .

EDIT: with a value. I need to do it with a null value.
 

Attachments

  • Picture2.png
    Picture2.png
    123.3 KB · Views: 165

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
Visual when I remove the text value and leave the cell.....
 

Attachments

  • Picture3.png
    Picture3.png
    115.5 KB · Views: 194

CJ_London

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2013
Messages
16,553
so that is a zls (not a null) as I suspected - now step through the code to the selstart line - what do you see as a value now?
 

dgreen

Member
Local time
Today, 03:00
Joined
Sep 30, 2018
Messages
397
@CJ_London when I tried the technique of commenting out the .SetFocus, the spell checker doesn't error BUT moves to a new field in another record (Not Requested and not coded that way)

Just a thought - to set the selLength property, the control must have the focus - your code is in the afterupdate event which occurs before the lostfocus event so it already has the focus. Suggest comment out your setFocus line to see if that makes a difference - My thinking is that the isnull line is allowing through a zls and the setfocus 'resets' it to null

cure might be

If nz(Me.Purpose_Agenda,"")<>"" then
With Me.Purpose_Agenda
 

Users who are viewing this thread

Top Bottom