acCmdSpelling doesn't move out of field when spell check is done

[mreinsmith]

Here is the code I am using to do a Spell Check on a Form Field

Private Sub Title_Exit(Cancel As Integer)

Call SpellCheckControl(Me, Title)

End Sub

Public Sub SpellCheckControl(frm As Form, ctl As Control)

With Forms(frm.Name).Controls(ctl.Name)

.SetFocus

  If Len(.Value) > 0 Then
 
    DoCmd.SetWarnings False
    .SelStart = 1
    .SelLength = Len(.Value)
    DoCmd.RunCommand acCmdSpelling
     .SelLength = 0
    DoCmd.SetWarnings True
  End If

End With

End Sub

Any time I move/tab out of the field I have to do it twice

How can I avoid this problem?

Thanks

 

[GPGeorge]

Your code explicitly moves the focus to the control called "Title". And since it is called from "Title" when focus leaves that control, you're putting the cursor right back where it came from. And that means you must move it again.

I suggest you put this code on the BeforeUpdate event, instead. That's the event used to validate values in controls.
Of course, that also means eliminating some of the focus shifting lines in the sub.

Or, there might be an alternative involving moving the focus to a different control at the end of the Sub.

 

[mreinsmith]

As I understand it, you have to set focus for the spell check to work in this case (maybe that's not right)

Beforeupdate should work, going to give that a shot (can't believe I didn't think of it

THANKS!

Matt

PS, I tried move focus, but I was using afterupdate, before update should have fixed that too

 

[mreinsmith]

Great that works, Next issue..

I have the exact same procedure in the next field, the "Description" Field

Now, when I tab out of that I get the error

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Marketplace DB from saving the data in the field.

* If this is a macro, open the macro in the Macro window and remove the action that forces a save (for example, GoToControl).
* If the macro includes a SetValue action, set the macro to the AfterUpdate property of the control instead.
* If this is a function, redefine the function in the Module window.

However, If I cancel the spelling correction does save, but I have to tab out of that field.

That Description Field is set to Rich Text, could that be the issue? I don't want to lose the formatting in my data, so I'd have to set up a test to try it.

Thanks

 

[mreinsmith]

Great that works, Next issue..

I have the exact same procedure in the next field, the "Description" Field

Now, when I tab out of that I get the error

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Marketplace DB from saving the data in the field.

* If this is a macro, open the macro in the Macro window and remove the action that forces a save (for example, GoToControl).
* If the macro includes a SetValue action, set the macro to the AfterUpdate property of the control instead.
* If this is a function, redefine the function in the Module window.

However, If I cancel the spelling correction does save, but I have to tab out of that field.

That Description Field is set to Rich Text, could that be the issue? I don't want to lose the formatting in my data, so I'd have to set up a test to try it.

Thanks

Correction, it DOES tab out of the field, but the spelling error is NOT corrected and saved

 

[GPGeorge]

As I understand it, you have to set focus for the spell check to work in this case (maybe that's not right)

Beforeupdate should work, going to give that a shot (can't believe I didn't think of it

THANKS!

Matt

PS, I tried move focus, but I was using afterupdate, before update should have fixed that too

If you run your sub in the Before Update event of Title, Title already has focus.... No need to move it.

AfterUpdate runs after the focus has moved, so, no, it won't work the same way.

The other problem is more intractable. I can see why it won't work if the spell checker changes something and then tries to move on. The new spelling is now in the control, so Access again tries to run the BeforeUpdate event, and that puts it into an endless loop.

There is a better way, but I'm not aware of one off the top of my head at the moment.

 

[Gasman]

Here is what I have in the one place I have used it.

Private Sub txtnotes_Exit(Cancel As Integer)
Call SpellChecker(Me.ActiveControl)
Call SetBackColor(Me.ActiveControl)
End Sub

Public Sub SpellChecker(txt As TextBox)
    On Error GoTo Err_SpellChecker
    
    If Trim(txt & " ") <> "" 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
        'Debug.Print txt.Value
        DoCmd.SetWarnings True
    End If

Exit_SpellChecker:
    DoCmd.SetWarnings True
    Exit Sub
    
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 Sub

I probably copied the code from the same place you did, but when I spellcheck it lets me move to the next control?

 

[GPGeorge]

After a brief internet search

Here is what I have in the one place I have used it.

Private Sub txtnotes_Exit(Cancel As Integer)
Call SpellChecker(Me.ActiveControl)
Call SetBackColor(Me.ActiveControl)
End Sub

Public Sub SpellChecker(txt As TextBox)
    On Error GoTo Err_SpellChecker
   
    If Trim(txt & " ") <> "" 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
        'Debug.Print txt.Value
        DoCmd.SetWarnings True
    End If

Exit_SpellChecker:
    DoCmd.SetWarnings True
    Exit Sub
   
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 Sub

I probably copied the code from the same place you did, but when I spellcheck it lets me move to the next control?

What happens when it encounters a misspelled word? That's when I am getting the error loop.

 

[Gasman]

After a brief internet search

What happens when it encounters a misspelled word? That's when I am getting the error loop.

I will have to try tomorrow. I know it presents the dialogue to correct, but that is it.

 

[GPGeorge]

I figured out a way to alert the user to a misspelling, but it won't yet supply the correct spelling automatically.

This approach uses the Excel Spellcheck method, which is missing in Access. It can be placed in the Before Update event of a text control to check that control. If it contains a misspelling, the update is cancelled and the user warned.

Private Sub Title_BeforeUpdate(Cancel As Integer)

    With Me
        If .ActiveControl.OldValue <> .ActiveControl.Value Then
            If XLSpellchecker(Me.ActiveControl.Value) = False Then
                MsgBox .ActiveControl.Value & vbLf & "is not spelled Correctly" 
                Cancel = True
                .ActiveControl.Undo
            End If
        End If
    End With
     
End Sub

Private Function XLSpellchecker(strStringtoCheck As String) As Boolean

Dim XLSpellcheck As Object
 
    Set XLSpellcheck = New Excel.Application
    XLSpellchecker = XLSpellcheck.Application.CheckSpelling(strStringtoCheck)
    Set XLSpellcheck = Nothing
    
End Function

Also, because it creates an instance of Excel to run the Excel method, it's slow.

 

[mreinsmith]

I figured out a way to alert the user to a misspelling, but it won't yet supply the correct spelling automatically.

This approach uses the Excel Spellcheck method, which is missing in Access. It can be placed in the Before Update event of a text control to check that control. If it contains a misspelling, the update is cancelled and the user warned.

Private Sub Title_BeforeUpdate(Cancel As Integer)

    With Me
        If .ActiveControl.OldValue <> .ActiveControl.Value Then
            If XLSpellchecker(Me.ActiveControl.Value) = False Then
                MsgBox .ActiveControl.Value & vbLf & "is not spelled Correctly"
                Cancel = True
                .ActiveControl.Undo
            End If
        End If
    End With
    
End Sub

Private Function XLSpellchecker(strStringtoCheck As String) As Boolean

Dim XLSpellcheck As Object
 
    Set XLSpellcheck = New Excel.Application
    XLSpellchecker = XLSpellcheck.Application.CheckSpelling(strStringtoCheck)
    Set XLSpellcheck = Nothing
   
End Function

Also, because it creates an instance of Excel to run the Excel method, it's slow.

This is great, going to run through this and see what I can figure out

Calling Excel shouldn't be a problem as far as speed. Can you leave that instance active for when additional spell checks are run. Then close it when the form closes?

Plus it reminds me I need to start adding error processing, I've been neglecting it.

 

[GPGeorge]

This is great, going to run through this and see what I can figure out

Calling Excel shouldn't be a problem as far as speed. Can you leave that instance active for when additional spell checks are run. Then close it when the form closes?

Plus it reminds me I need to start adding error processing, I've been neglecting it.

If you mean creating the Excel instance for the form, I think so, but that's a bit beyond my ability.

In fact, now that I think about it, it probably ought to be a class too. Maybe one of the better coders can enhance the procedure.

 

[Gasman]

After a brief internet search

What happens when it encounters a misspelled word? That's when I am getting the error loop.

For a spelling error it presents the dialogue with suggestions. I select one and press change.
The next control is then in focus, which in this case is a button.

So basically it behaves the same way, except for showing the spelling dialogue when a mistake is made.

 

[DickyP]

Must admit I have used the Word spell-checker for years similar to the suggested use of Excel above. Works well.

 

[isladogs]

Here is a slightly modified version of the standard code. See if it helps:

Public Sub SpellCheckControl(ctlSpell As control)
' Adaptation by Terry Wickenden of code
' from Microsoft Knowledge Base

On Error GoTo Err_Handler

   If TypeOf ctlSpell Is TextBox Then
     If ctlSpell.Locked = False And ctlSpell.visible = True Then
        If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
          'MsgBox "There is nothing to spell check."
          ctlSpell.SetFocus
          Exit Sub
        End If
        With ctlSpell
          .SetFocus
          .SelStart = 0
          .SelLength = Len(ctlSpell)
        End With
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdSpelling
        DoCmd.SetWarnings True
      End If
   ElseIf TypeOf ctlSpell Is SubForm Then
     If ctlSpell.Locked = False And ctlSpell.visible = True Then
        If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
          'MsgBox "There is nothing to spell check."
          ctlSpell.SetFocus
          Exit Sub
        End If
        With ctlSpell
          .SetFocus
          .SelStart = 0
          .SelLength = Len(ctlSpell)
        End With
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdSpelling
        DoCmd.SetWarnings True
      End If
   Else
     MsgBox "Spell check is not available for this item."
   End If
   ctlSpell.SetFocus
  
  
Exit_Handler:
    Exit Sub

Err_Handler:
    strProc = "SpellCheckControl"
    MsgBox "Error " & Err.number & " in " & strProc & " procedure : " & Err.description
        
    Resume Exit_Handler
  
End Sub

 

[CJ_London]

Can you leave that instance active for when additional spell checks are run

You can declare as static. On my phone but would look something like

Private Function XLSpellchecker(strStringtoCheck As String) As Boolean

Static XLSpellcheck As Object
 
    If XLSpellcheck is nothing then Set XLSpellcheck = New Excel.Application

Put some sort of condition to set it to nothing when done. Perhaps something like
If strStringtoCheck=“*” then set XLSpellcheck=nothing