Auto Spell Check Issue

[Topflite66]

I am using Access 2016 and have tried running the below code as an On Exit Event to automatically spell check a Long Text field on one of my forms. When I Tab out of the field, if I spell something incorrectly, Spell Check pops up and I can correct the misspelled word and then it moves to the next field. However, the issue I am running into is that after it corrects the misspelled word it deletes the rest of the text in the box except for the word it corrected.

Private Sub AdditionalOpt_Exit(Cancel As Integer)


Dim strSpell
strSpell = AdditionalOpt
If IsNull(Len(strSpell)) Or Len(strSpell) = 0 Then
Exit Sub
End If
With AdditionalOpt
.SetFocus
.SelStart = 0
.SelLength = Len(strSpell)
End With
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True
End Sub

I have also tried using just the code after the "End With" statement as both On Exit and After Update Events. The code works to correct the misspelled word and doesn't delete the rest of the contents of the text box, however, the issue I have with that code is that it closes the record I'm currently working on and opens the first created record and wants to check every field on every record. Any suggestions would be appreciated. Thanks in Advance.

[June7]

I tested your code and do not experience the behavior you describe. The incorrect spelled word is replaced with the correct within the string. I did simplify code a little but shouldn't make any difference in behavior:

Dim strSpell
strSpell = Me.AdditionalOpt
If strSpell & "" <> "" Then
    With Me.AdditionalOpt
        .SetFocus
        .SelStart = 0
        .SelLength = Len(strSpell)
    End With
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSpelling
    DoCmd.SetWarnings True
End If

[Topflite66]

I tested your code and do not experience the behavior you describe. The incorrect spelled word is replaced with the correct within the string. I did simplify code a little but shouldn't make any difference in behavior:[/code]

Strange. I tried your simplified code and it does the same thing. I purposefully misspelled a word in a string, the spell checker opened after I tabbed out of the text box. I corrected the spelling and clicked Change. Once I click on the Change button all the other text gets deleted except for the word I just corrected. Any thoughts? I've been messing with this for a couple days now so getting a little frustrated. TIA


**Additional Info:** I have two other text fields similar to the Additional Options text field. So I added the code to one of them and something goofy happened. The Tab Index for the Additional Options is 20, Action 31 and Notes 32. I have the simplified code as an On Exit Event for both Additional Options and Action. I opened up the last record and mistyped a word in the Action text field and hit TAB. Spell check did not open and did not give me any indication that it ran but the cursor jumped to the Additional Options text box.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Topflite66]

If you want to provide db for analysis, follow instructions at bottom of my post.

I'll have to do that tomorrow. Leaving for the day. Something goofy is happening as I remarked out from the Additional Options text field. So the only working spell check code was connected to the Action field and it did the same thing. I mistyped a word, hit tab and the cursor moved to the Additional Options field. I will upload first thing tomorrow morning. Thank you for your help.

[Topflite66]

Copy to Upload.zip

If you want to provide db for analysis, follow instructions at bottom of my post.

Attached is the db. The only form Vehicle Order Form is the one that I'm having issues with. Thank you for your help.

[June7]

Testing the form. The spell checker never opens. I confirm code executes. Very odd.

I cannot replicate issue in my db and cannot see why it occurs in yours. I tried copying your form and removing all code except for the 2 Exit events and still get the issue. I created a new form in your db with 3 UNBOUND textboxes and spell check code and it works. Can only say that weirdness is often due to corruption that cannot be explained nor fixed. Try building new form, in stages.


BTW, every module should have Option Explicit in header. Access can be set to do this by default. On the VBA Editor menu: Tools>Options>General>check Require Variable Declaration.

[Topflite66]

Can only say that weirdness is often due to corruption that cannot be explained nor fixed. Try building new form, in stages.
BTW, every module should have Option Explicit in header. Access can be set to do this by default. On the VBA Editor menu: Tools>Options>General>check Require Variable Declaration.

Thank you for looking into this for me. At least now I know that it's not just me. LOL I will build a new form and hopefully that will fix the issue. Also thank you for the tip on the Option Explicit.

[June7]

Will have to manually add the Option Explicit line to existing modules.

[Topflite66]

Will have to manually add the Option Explicit line to existing modules.

I rebuilt the form and had the same issue. So I tried building a separate table in my db and a form with one field and the code worked. So I took it a step further and created a form based on tbl_VehicleOders. Then I entered your simplified code in only the Action field. So the only code associated with that form was the code in the Action field. I get the same result, it corrects the misspelled word but deletes the rest of the text. So I am guessing there is something in the table that is causing this issue? I have looked at the table but am not able to determine what would cause this. Any ideas? I'm baffled. Thank you.

[June7]

Well, I did create a new form and ran code with your table and was successful. So not sure how the issue is wholly with the table data.

[Topflite66]

Well, I did create a new form and ran code with your table and was successful. So not sure how the issue is wholly with the table data.

I'm really baffled now.

I tried it again and got a similar result. I unzipped the file I uploaded, selected the tbl_VehicleOrders table, and created a new form. Then I saved and closed the form as tbl_VehicleOrders Form, re-opened it in design view and selected the On Exit event for the Action field. I copied and pasted your code from post 2, changing lines 2 and 4 from AdditionalOPt to Action. Saved and closed the VBA window, saved and closed the form. Re-opened the form to record 1. Typed "Mudflaps, block heater" in the action field. The spell check opened up so I selected the first option Mud flaps from the list, and clicked on change. It made the change but deleted the ", block heater" part of the string. The other thing I tried was to ignore it the first time then went back into the Action field and hit tab, and the spellcheck would open. I selected the first option again, Mud flaps and clicked change and that time it would make the change and leave the rest of the string as is.

It was consistently the same if I changed the misspelled word the first time it would delete the rest of the string. However, if I ignored the misspelling the first time and went back into the field then fixed the misspelled word, it would work correctly. Same case in my active db also.

One thing I noticed, which I'm not sure if it's relevant or not, when I would Ignore the misspelling the first time the words in the Suggestions box were all caps and then when I would go back to the Action field and hit tab the words in the Suggestions box were all lower case.

[Topflite66]

**Update**

It's a Capitalization issue. For the tbl_VehicleOrders table, the Action field under format I have the > indicator to capitalize everything so that when I ran my reports everything was capitalized. I also, for the report, had the Action field format option with the > to capitalize everything when viewing the individual records.

If I remove the > for the table and leave the > for the form, I have the same results as noted above. If I removed the > for the form and add the > back into the table, everything works perfect. If I remove the > from both the code works again.

I don't necessarily need the individual records in all caps so will just remove the > from the form and press on. Unless you see an issue with the options I have selected for working with the spellcheck that would fix that issue? Not quite sure what capitalization has to do with the text getting deleted but for whatever reason that's what was happening. Thank you for your help.