Spell check individual control

Here's what I finally got to work.  Calling the spell checker seems to make Access loose track of what you are doing and it ends up in a loop.  On this particular subform, I had tried the code I originally posted and I called the spell checker from the BeforeUpdate() event of the form as the last line of code.  I wanted to make sure that all the form and control level edits were completed before I got into fixing spelling errors.  But even though the record got saved, the BeforeUpdate event never completed and the AfterUpdate event didn't run.  So, the spell checker is interfering with the event model in Access.

Prompted by TLH's suggestion I went back to the field by field approach which I had abandoned earlier for other reasons and finally figured out a solution that seems to work.  I call the spell checker from the control's LostFocus event but ONLY if the field was updated.  Then the next statement moves focus to the next logical control.  The trick comes at the end.  The last control in the set has a GoToRecord to move the record pointer to a new record.  Without that, you end up not being able to get out of the subform.

Private Sub txtNeeds_LostFocus()
    If Me.txtNeeds & "" <> Me.txtNeeds.OldValue & "" Then     'only spell check if dirty
        Call SpellChecker(Me)
        Me.txtCHCPService.SetFocus
    End If
End Sub
Private Sub txtCHCPService_LostFocus()
    If Me.txtCHCPService & "" <> Me.txtCHCPService.OldValue & "" Then     'only spell check if dirty
        Call SpellChecker(Me)
        Me.txtMeasuredOutcome.SetFocus
    End If
End Sub
Private Sub txtMeasuredOutcome_LostFocus()
    If Me.txtMeasuredOutcome & "" <> Me.txtMeasuredOutcome.OldValue & "" Then     'only spell check if dirty
        Call SpellChecker(Me)
        DoCmd.GoToRecord , "", acNext
    End If
End Sub

Public Function SpellChecker(Calling As Form)

 Dim ctlSpell As Control

  DoCmd.RunMacro "mWarningsOff"
 Set ctlSpell = Calling.ActiveControl
 If (ctlSpell.Locked) Then
    ''Ignore
 Else
    If Len(ctlSpell) > 0 Then
        With ctlSpell
        .SetFocus
        .SelStart = 0
        .SelLength = Len(ctlSpell)
        End With
        DoCmd.RunCommand acCmdSpelling
    End If
End If
 DoCmd.RunMacro "mWarningsOn"
End Function

Select all Open in new window

Note that in TLH's original code he set warnings Off but never set them back on.  This is extremely dangerous as anyone who has been burned by having warnings off will attest.  Warnings off not only stops the annoying messages you don't want your user to see, it also stops the "are you sure you want to close without saving" message that you most certainly want to see if you modify a form and than close it without explicitly saving.

To avoid this issue in my applications, I never just turn warnings off.  I created two macros (the ONLY macros in my applications) .  In addition to turning the warnings off, the macro turns the hourglass on and vice versa.  That way if I inadvertently forget to turn the warnings back on, I have a visible clue that something is amiss.