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

[MajP]

As a Class there are some problems in structure.
1. The way it is set up now is really slow. You are creating an instance of your class on every call to the before update. Create it once and then use it for the life of the form. You could do this on the first before update, but it takes time for Word to load in the background. I would just do it on form load and after that it is very fast.
2. You pass in a form instance to your spelling check but do not use it in your code. No need for it.
3. I modified the function to return a list of words that are misSpelled. This gives the user a chance to determine if they want to spell check or not
Then I relied on the built in spell checker.

Private Sub ErrorDescription_BeforeUpdate(Cancel As Integer)
    '1. This is a lot of overhead to create a whole new instance of WDSC on every before update. Works best to set it once in the form load
    ' Set WDSC = New WDSpellCheck
    If WDSC Is Nothing Then Set WDSC = New WDSpellCheck
 
   '2. Do not need to pass the form since not used
    Dim MisSpelledWords As String
    MisSpelledWords = WDSC.MySpellCheck(Nz(Me.ActiveControl.OldValue, vbNullString), Nz(Me.ActiveControl.Value, vbNullString))
    If MisSpelledWords <> "" Then
       Dim rtn As String
       rtn = MsgBox("The following words were potentially mispelled:" & vbCrLf & vbCrLf & MisSpelledWords & vbCrLf & vbCrLf & "If you would like to spell check these then select YES.", vbYesNo, "Mispelled")
       If rtn = vbYes Then
         RunSpellCheck = True
       Else
         RunSpellCheck = False
       End If
     End If
End Sub

In your class you have a property Application you should set it once, and then just call it. You seem to do this multiple times even using local instances. You are defeating the purpose of a class the way this is structured

Public Function MySpellCheck(strToCheckOld As String, StrToCheckNew As String) As String
 
'1. You pass in the form and it is unused
' MySpellCheck(frm As Access.Form, strToCheckOld As String, StrToCheckNew As String) As Boolean
'2. You create a local variable with the same name as the class variable and then create another instance of WDSpellCheck
'Dim WDSPC As WDSpellCheck
  Dim aryStringtoCheck() As String
  Dim intAryIndex As Integer
  varStatusWait = SysCmd(acSysCmdSetStatus, "Invoking Your Custom Spellchecker. Please wait....")
'3. The below defeats the purpose of a class module
    'Set WDSPC = New WDSpellCheck
   
   '4. WHY pass in the Form? Unused.
   ' With frm
        If Nz(strToCheckOld, vbNullString) <> Nz(StrToCheckNew, vbNullString) Then
            aryStringtoCheck() = Split(Nz(StrToCheckNew, vbNullString), " ")
           mySpellCheck = ""
           For intAryIndex = 0 To UBound(aryStringtoCheck)
                If Me.Application.checkspelling(aryStringtoCheck(intAryIndex)) = False Then  '5. Use Me.application
                    'True means error
                    'pass back the word
                    MySpellCheck = MySpellCheck & aryStringtoCheck(intAryIndex) & vbCrLf
                    'If MsgBox("""" & aryStringtoCheck(intAryIndex) & """" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "is not spelled correctly." & Chr(13) & Chr(10) & _
                    '    "Correct the spelling or add the misspelled word to the dictionary.", vbYesNo, "Change or Add Word") = vbYes Then
                    '     Call AddToDict(aryStringtoCheck(intAryIndex))
                    'End If
                End If
            Next intAryIndex
        End If
    ' End With
    varStatusWait = SysCmd(acSysCmdClearStatus)

End Function

Trying to run the spell check in the before update not possible. See if this version using some of the class and the built in spell checker makes sense. Gives you a little of both. It is not truly before update, but provides much more flexibility
1. It alerts you if there are potential errors.
2. Allows you to decide if you want to spell check
3. Runs real spell check.

I would likely stick with using the built in spell checker. I always find these "unseen" automation instances to get hung up and the next thing you know you have 20 instances of Word running in the background. The first example uses a modified version of the word class, the second example uses just built in spelling.

 

[GPGeorge]

Thanks for the feedback. Although this is built around the spell checker, my primary goal was to try to understand classes and how to implement them.
I was well aware of problems in this V1, particularly around calling the classes repeatedly; I figured that was a bad idea and I was hoping there would be a better way to do things.

I'm also not opposed to invoking the built-in Access SpellChecker. In fact, last night I created my own V2 which calls Access' Spellchecker to compare the two approaches.

With regard to passing in the form reference, yup. I started out thinking one thing and neglected to clean up the arguments that I didn't need when I abandoned that approach.

 

[MajP]

particularly around calling the classes repeatedly; I figured that was a bad idea and I was hoping there would be a better way to do things.

The biggest issue I saw was you have a class property called Application which holds a reference to a Word Application, but you where not directly using it.
Once you set that in the class instantiation you open it and keep a reference to it.
So for the life of the instantiated class you simply need to refer to it as Me.Application from within the class. Or from the outside as WDSC.Application

There are some limitations of the built in spell checker where and when you can use it. AFAIK it will not work on a modal form. I think where you might get some utility is to fully build a spell checker pop up form with the same features as the normal spell checker window. Since Access does not expose any spelling checker object you could stick with word.
1. Push the text from the control to your form (probably open args). Show in textbox
2. Loop the words
3. Check for bad spelling highlight bad word using checkspelling
4. Return the spellingSuggestions collection and populate a listbox with the suggestions
4a. Click on an item in the listbox and swap with the selected word
4b. allow user to ignore
4c. continue if the user edited the text by hand
4d. Allow ignore all by saving to a dictionary
4e. Allow replace all
5. have options to modify search critieria
6. Push finished text back to the control

I think you could roll all the functionality of a standard spell checker window, but this may give you more flexibility when and where you can use it. This may be a lot of work for little payoff, but it still would be a good exercise in code and class design. I think if you did build it, it might come in handy in a lot of places where you run into limitations with calling the regular spell checker.
Might have to use Regexp to do you find and replace. Since you only want to replace full words.

 

[isladogs]

Some time last year, I worked on a method of spell checking selected columns in a datasheet.
I used the built in spell checker and it worked fine
The tricky bit was selecting specified column(s) in code.
The approach I used may be of interest here:

Datasheet Row / Column Select & Spell Check

Datasheets allow you to select columns from the column header and rows using the record selector. Surprisingly there appears to be no simple direct command available to do this programmatically. A no-code approach is to add the Spelling command to the right click context menu for datasheet...

isladogs.co.uk

 

[Imb]

I used the built in spell checker and it worked fine
The tricky bit was selecting specified column(s) in code.

Perhaps a little out of topic for the OP.

Working with dynamical forms make these things very easy.
In each control, where-ever it is situated, I can (dbl)click on a control to open a Zoom-form.
In this Zoom-form you find much functionality to "manipulete" the value of the control.
E.g. (the build-in) Spelling_checker, Translate to almost any language, word- or character-statistics, conversion (or part of) to uppercase, lowercase, Replacement of texts, etc. Depending on authorization and other context, it can be stored in the appropriate field.
If necessary, different Spelling_checkers can be added.

Regarding selecting column(s).
I do not use datasheets, because there is hardly control over the fields/controls. Instead I use a continuous (dynamical) form that is completely automatic generated for every Table/Identity.
Clicking on the header opens a form with many functions acting on colums: make the column non-/editable, colour a column with any colour, histogram. (desc) sorting on alfabet or on length, increase/decrease width of the column, change values in the column depending on a criterion, but depending on authorization, etc.
Even sorting after the first space could be an option. This functionality is build-in for any table in all applications.


Imb.

 

[GPGeorge]

The biggest issue I saw was you have a class property called Application which holds a reference to a Word Application, but you where not directly using it.
Once you set that in the class instantiation you open it and keep a reference to it.
So for the life of the instantiated class you simply need to refer to it as Me.Application from within the class. Or from the outside as WDSC.Application

There are some limitations of the built in spell checker where and when you can use it. AFAIK it will not work on a modal form. I think where you might get some utility is to fully build a spell checker pop up form with the same features as the normal spell checker window. Since Access does not expose any spelling checker object you could stick with word.
1. Push the text from the control to your form (probably open args). Show in textbox
2. Loop the words
3. Check for bad spelling highlight bad word using checkspelling
4. Return the spellingSuggestions collection and populate a listbox with the suggestions
4a. Click on an item in the listbox and swap with the selected word
4b. allow user to ignore
4c. continue if the user edited the text by hand
4d. Allow ignore all by saving to a dictionary
4e. Allow replace all
5. have options to modify search critieria
6. Push finished text back to the control

I think you could roll all the functionality of a standard spell checker window, but this may give you more flexibility when and where you can use it. This may be a lot of work for little payoff, but it still would be a good exercise in code and class design. I think if you did build it, it might come in handy in a lot of places where you run into limitations with calling the regular spell checker.
Might have to use Regexp to do you find and replace. Since you only want to replace full words.

I haven't yet had time to work through all of the generous suggestions you've made. I very much appreciate your sharing your expertise.

Today should be free of other distractions.

To the comments above.

Application is, in fact, used, but it is ambiguous. I violated one of my own rules there, in fact.

Application is a reserved word, so of course, it's misleading.

Here:

If WDSPC.Application.checkspelling(aryStringtoCheck(intAryIndex)) = False Then

In that line, Application is a call to the Class property; without it an error is raised when that line tries to execute. Why? That's one of the things I hope to learn.

Once again, this spell checker is a vehicle to try to work through creating classes in VBA. I recognize that this is a long overdue effort on my part. But better late than never.

I really don't worry so much about implementing this as a useful tool; I'm not doing any serious data entry these days.

On the other hand, I am worried about putting off the day when I can't learn something new about Access.