Word.Basic ToolsSpelling won't work in Access 2010

[stav.kaufman]

Hi all.


I'm new here, and new to Access 2010 (have been working with Access 2003 for the last few years).
I'd appreciate any help.

I'm converting our mdb to an accdb. It seems to work almost fine, except for some bugging problems.


We used to use a Word.Basic object for spell checking a certain text box. The spell checker still works, but it's in the background and I can't bring it to the front! The same code brought it to the front in Office 2003! weird.

Below's the code snippet.
Thanks in advance.
Stav

Ah, by the way- I'm working on a 64bit machine.
-------------------------------------------------------------------------
Public Function SpellCheck(Textbx As TextBox)
On Error GoTo Err_SpellCheck

Dim sBadSpelling As String
Dim sTmpString As String

If Not IsNull(oWDBasic) Then
Set oWDBasic = CreateObject("Word.Basic")
Textbx.SetFocus
sBadSpelling = Textbx.text
oWDBasic.FileNew
oWDBasic.INSERT sBadSpelling
'On Error Resume Next
oWDBasic.ToolsSpelling
oWDBasic.AppHide
oWDBasic.EditSelectAll
oWDBasic.SetDocumentVar "MyVar", oWDBasic.Selection
sTmpString = oWDBasic.GetDocumentVar("MyVar")
sBadSpelling = Left(sTmpString, Len(sTmpString) - 1)
Textbx.Value = sBadSpelling
oWDBasic.FileClose 2

' close down the word application

oWDBasic.AppClose

Set oWDBasic = Nothing

MsgBox "Spell Check is complete. text is: " & sTmpString
End If

Exit_SpellCheck:

Exit Function

Err_SpellCheck:
MsgBox "Error in SpellCheck : " & err.Description
Resume Exit_SpellCheck

End Function

[stav.kaufman]

I have been searching the web endlessly... !

[HiTechCoach]

Note: There are very few people using Access 2010 since it is so new. Even 2007 does not have a lot of users. Finding published information on the web specifically related to Access 2010 is very limited at best.

This might help: Converting to Access 2007 or 2010

Knowing that you are working on a 64-bit machine is not as important as knowing if you are working on the 64-bit version Office/Access . What version of Office/Access 2010 are you using (32 bit or 64 bit)?

I have not seen calling Word from Access just to use the spell checker before. Interesting ...

The same spell check can be called directly from within Access. As far as I know starting in Office 2000 the spell checker is a common/shared component by all apps in the Office Suite.

Curious, what is the advantage of using Word instead of Access to open the spell checker?

[stav.kaufman]

Boyd, thanks for the reply.

I'm using the 64bit version of Office 2010. I should have stated that.
I'm not sure why my predecessors used word for the spelling. I saw in previous threads that one can use acCmdSpelling.

I just solved the issue using this command (thanks to: Steve R, http://www.access-programmers.co.uk/.../t-157586.html), and I'll write down the solution, for future viewers:

I had some problems using acCmdSpelling in the After Update event:

*Either it checked all the controls instead of the selected one,
*Or- it kept giving an error when trying to modify the text ("The macro or function set to the Before Update or Validation Rule Property for this field is preventing Microsoft Access from saving the data in the field").
*Using the command in an On Exit or On Modify event resulted in multiple redundant spelling checks.

I solved it using a boolean variable to mark whether the text was already spell checked, and using acCmdSpelling in the On Exit event.

The code is below:

1) Public bolTextChanged As Boolean
2) On Change: bolTextChanged = True
3) On Exit:
DoCmd.SetWarnings False
With Me.MyControl
.SetFocus
.SelStart = 0
.SelLength = Nz(Len(Me.MyControl), 0)
If Nz(Len(Me.MyControl), 0) > 0 And bolTextChanged Then Me.MyControl.SetFocus: DoCmd.RunCommand acCmdSpelling
End With
DoCmd.SetWarnings True
bolTextChanged = False

Hope this helps future searchers ,
Stav

[HiTechCoach]

The On Change event fires are you type in the control. Not useful for spell checking.

The On Exit event fires whenever you leave the control. Even if the control has not been change. Not very useful for spell checking as it would also fire off even if no value was entered into the control or nothing was changed.

Because you are using the above two event, you are having to write a lot more code than needed.

You should also check to be sure that the control has a value before trying to run the spell check.

The ideal event is the After Update event. It will only fire when you exit the control if the value in the control has changed. That is exactly when we need to spell check.

Example code to Spell Check using the After Update event of a control named Notes:

Private Sub Notes_AfterUpdate()
 
DoCmd.SetWarnings False
 
   With Me![Notes]
 
   ' make sure that there is something to spell check
     If Len(.Value) > 0 Then
 
         ' select the text in the control
            .SetFocus
            .SelStart = 1
            .SelLength = Len(.Value)
 
         ' run the spell check
            DoCmd.RunCommand acCmdSpelling
 
          ' unselect the text
            .SelLength = 0
 
        End If
 
    End With
 
DoCmd.SetWarnings True
 
End Sub

[stav.kaufman]

Dear Boyd,
Thanks for your continued interest.

I totally agree with what you wrote,
only, as I wrote before, when using the code in an On Update event,
I get an error message whenever I try to change the text using the speller:
"The macro or function set to the Before Update or Validation Rule Property for this field is preventing Microsoft Access from saving the data in the field".

The code I gave is essentially the same as the one you gave (but I tried using yours in the On Update, just to make sure. Same error...).

I am doing the spell check in the On Exit and using the boolean var only as a workaround, because I couldn't overcome the problem with that error message in any other way.

I'd be happy to get a better solution.

Thanks,
Stav

[HiTechCoach]

I have not tried this code yet in Access 2010. I will give it a test to see if I get the same error with the 32-bit version.

I will also try your Word spell check code to see if it will work in the 32 bit version. Curious to see if it works in the 32-bit veriosn.

I will let you know what I find out.

Note:
I have not started using the 64-bit version of Office for testing or development due to all the bug/issues. From my testing the 32-bit version so far works much better than the 64-bit version. In my opinion neither are really ready for production use. By SP2 update it usually is working well enough to use a production environment. Hopefully when SP1 is released it will stable enogh for production. Until then I am not willing to support any version of Office 2010 in a production environment, especially the 64-bit version.