Spell Check code in MS Access form field - throws error when change is accepted

Question

I added the following code in the AfterUpdate event of a textbox in an MS Access form:

Private Sub txtComments_AfterUpdate()
With Me!txtComments
    .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

This runs a spell check when the user exits the field. It partially works. It opens the spell check dialogue, and locates the first error. The problem is, when you click Ignore, Change, etc to handle/repair the spelling error, the code fails and the following error box appears:

"The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field."

I tried adding record-saving code before the spell check code:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

but this didn't solve it.

Did you see http://answers.microsoft.com/en-us/office/forum/office_2007-access/can-i-trigger-spellcheck-programatically-in-access ? — Fionnuala, Aug 02 '12 at 19:22

score 6 · Accepted Answer · answered Aug 02 '12 at 20:32

6

This code works as the On Exit event (instead of After Update).

Private Sub txtComments_Exit(Cancel As Integer)
With Me!txtComments
    .SetFocus
    If Len(.value) > 0 Then
        .SelStart = 1
        .SelLength = Len(.value)
        DoCmd.RunCommand acCmdSpelling
        .SelLength = 0
    End If
End With
End Sub

answered Aug 02 '12 at 20:32

HansUp

95,961
11
77
135

The On Exit event will run every time someone tabs through the control and the user will be prompted by the spelling pop-up. – Fionnuala Aug 03 '12 at 03:41
1

True but they are only prompted if there are spelling errors to manage. – Michael T Aug 06 '12 at 13:46
Add DoCmd.SetWarnings = False to suppress the message box if the current text has no spelling errors: https://csharpdeveloper.wordpress.com/2021/02/25/programmatically-invoke-microsoft-access-spellchecker-using-vba-accmdspelling/ – Rob Kraft Feb 25 '21 at 15:52

Fionnuala · Answer 2 · 2014-01-09T21:03:59.833

3

Using an update event associated with the control is not going to work, because each change triggers the event again. You need a button, or such like:

Private Sub Spell_Click()
    With Me!txtComments
        .SetFocus
        .SelStart = 0
        .SelLength = Len(Me!txtComments)
    End With
    DoCmd.RunCommand acCmdSpelling
End Sub

It would be possible to avoid some of the problems with the On Exit event by the addition of a line:

    If Me.txtComments.Value <> Me.txtComments.OldValue Then
       With Me!txtComments
           .SetFocus
           .SelStart = 0
          .SelLength = Len(Me!txtComments)
       End With
    <...>

At least this will only run when you pass through the control until the record is saved, not every time, whether txtComments is changed or not.

edited Jan 09 '14 at 21:03

answered Aug 02 '12 at 19:15

Fionnuala

90,370
7
114
152

Ok I spoke too soon. If I click Ignore this works, but if I click Change to change the value, then I get the error again. The act of changing the data triggers another spell check event and errors out. – Michael T Aug 02 '12 at 20:41
I tested and accepted the changes and it worked for me. Are you running from a button or from the events belonging to the control? – Fionnuala Aug 02 '12 at 20:43
1

It runs from the events belonging to the text box. I need it to run automatically when the user adds text, not from a separate button. HansUp's solution (putting it in the OnExit event) solved it. – Michael T Aug 02 '12 at 21:05
Remou, in your second example -you forgot to include the most important line: DoCmd.RunCommand acCmdSpelling – Parfait Jan 09 '14 at 20:52

Spell Check code in MS Access form field - throws error when change is accepted

2 Answers2