VBA to run custom spell checker and log errors in table?

[tetsuo2030]

Greetings all.

I've stolen/used this code as a spell checker on forms.

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

What I want to do is:

-loop through specific fields in a recordset
-log every spelling error (and its associated field) in a table
-display the results in a form
-return a count of the errors found

need help with the bold part.

I did a little reading, but it looks like all DoCmd.RunComman d acCmdSpelling does is launch the equivalent of a the spell checker--which, I don't believe, I can manipulate/capture.

So, how does the spell checker work? I imagine one could write code to capture a string of consecutive characters (i.e. a space before and after) and check that string against some sort of MS Office dictionary.

Any ideas how to do that?

[ADezii]

Just subscribing, will return later with a possible solution...

[zmbd]

tetsuo2030:
- Capturing the spellcheck statistics
I haven’t seen a direct method within VBA for doing this; however, at controlled-use-access-spell-check-possible:Post#5 that has a function where in the built in speller is called. Looks like there will need to be some tweaking… so that the code becomes:

 Public Function SpellChecker(Calling As Form)
    Dim ctlSpell As Control
    Dim Incoming As String, Outgoing As String
    Dim line1 As Variant
    'Dim mbResult As Long
    DoCmd.SetWarnings False
    Set ctlSpell = Calling.ActiveControl
    If (ctlSpell.Locked) Then
        line1 = "Cannot spell check. Field is read-only"
        SpellChecker = MsgBox(line1, vbOKOnly)
    Else
        If Len(ctlSpell) > 0 Then
            Incoming = ctlSpell
            With ctlSpell
                .SetFocus
                .SelStart = 0
                .SelLength = Len(ctlSpell)
            End With
            DoCmd.RunCommand acCmdSpelling
            Outgoing = ctlSpell
        End If
        ' See if any changes were made 09/29/04
        If (Incoming <> Outgoing) Then
            ' Notify user that changes
            ' were made, if you want to,
            ' or give Bronx cheer
        End If
    End If
End Function

You could then use the logic at line 23 to collect your stats

Call it from your control as described in the post by using something like this:

Private Sub txtexample_LostFocus()
    SpellChecker Me
End Sub

I'm sure that you can modify it from here to start looping thru the forms collection as an outer loop and then call and loop thru the controls collection on each form.


Otherwise:
To create a custom spell checker would be quite outside of the realm of what we can do to help; however, we can provide specific help with your code should you run into an issue during development.
The basics, of course, would be that you would need some sort of source to compare with the user entries, and then you may need to consider rather you want a literal match or maybe do approximate matching; there are two articles here on Bytes.com that cover two such methods:
ngram aproximate string matching
levenshtein approximate string matching
Then be prepared for a slow time going.

On Allen Brownes site (http://allenbrowne.com/links.html) there is a link to an open source spell checker: http://www.pcesoft.com/Access-Spell-...urce-Code.html that you can incorporate into your application. However, I don't think you can capture the statistics with it.

(I see that ADezii has taken an interest and does some of the most interesting coding!)

[ADezii]

The concept is to:

1. Create a Recordset and Loop thru each Field in every Record.
2. Pass the Value in each Field along with the Field Name to a Public Function.
3. This Public Function will use Automation Code using (Microsoft Word), and invoke Word's Spell Checker to check each Value for misspelling.
4. If a misspelling is found, write the Field Name and the misspelled Value to a Results Table.
5. Keep a Running Count of the number of misspellings and output that in some manner.
6. Set the Record Source of a Form to the Results Table for viewing.
7. This is the concept, the implementation of this concept is much more difficult, but I'll see what I can come up with.

[ADezii]

I have actually arrived at a rather simple solution using some Excel Automation Code and it's Spell Checking capability. I will post it in its entirety sometime tomorrow. Interesting challenge!

[ADezii]

As stated yesterday, I have created a solution that appears to work quite well, but hold on to your seat:

1. The entire Logic is build around a Table named Table1 with two Fields named Field1 and Field2 but should work equally well with any Table containing any number of Fields and any amount of Records.
   

   'Table1 definition
   Field1	Field2
   Alpha	 Marrch
   Tangoo	April
   Charlie   May
   Foxxtrot  June
   Braavo	Jully
   Zuluu	 August
   Dellta	September
   Lima	  Novemberr

2. Create an exact Copy of Table1 (structure only) and name it tblResults. This Table will contain the Field Names along with the misspellings.
3. Copy-N-Paste the following to a Standard Code Module in order to create a Global 'Instance' of Microsoft Excel.
   

   'Create a 'Global' Instance of Microsoft Excel in order to use
   'its Spell Checking capabilities.
   Public appExcel As New Excel.Application

4. Copy-N-Paste the following Function Definition to a Standard Code Module. This Function will verify each Field Value in every Record to see if it is spelled correctly.
   

   Public Function fSpellCheck2(strFieldName As String, strStringToCheck As String) As String
   'This Function will accept every Field value in every Record and analyze the Field
   'Values to see if there is a misspelling. If the Value represented by strStringToCheck
   'is misspelled (CheckSpelling(<strStringToCheck>)=False), write the Field Name as well
   'as the misspelled Value to a Table (tblResults).
   If Not appExcel.CheckSpelling(strStringToCheck) Then
     CurrentDb.Execute "INSERT INTO tblResults ([Field1],[Field2]) VALUES ('" & _
                       strFieldName & "', '" & strStringToCheck & "')", dbFailOnError
   End If
   End Function

   Copy-N-Paste the Primary Code to any place where it can be executed. This Code will process the Recordset and pass the appropriate Arguments to fSpellCheck2().
   

   Private Sub Command21_Click()
   On Error GoTo Err_Command21_Click
   Dim MyDB As Database
   Dim rst As DAO.Recordset
   Dim intNumOfFields As Integer
   Dim intFldCtr As Integer
   
   Set MyDB = CurrentDb()
   Set rst = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
   
   'Clear the Results Table by DELETING all existing Records
   CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
   
   intNumOfFields = rst.Fields.Count
   
   With rst
     'Process every single Field in every Record and pass the Field
     'Name as well as the actual Value to fSpellCheck2() for analysis
     Do While Not .EOF
       For intFldCtr = 0 To .Fields.Count - 1
          Call fSpellCheck2(.Fields(intFldCtr).Name, ![Field1])
           .MoveNext
       Next
     Loop
   End With
   
   'The number of Errors (misspellings) will actually be the number
   'of Records contained within the Results Table (tblResults)
   MsgBox "Number of Spelling Errors: " & DCount("*", "tblResults")
   
   'Don't forget to clean up the mess you created, especially with the
   'Global Object represented by appExcel
   rst.Close
   Set rst = Nothing
   
   appExcel.Quit
   Set appExcel = Nothing
   '************************* End of Clean Up ************************
   
   Exit_Command21_Click:
     Exit Sub
   
   Err_Command21_Click:
     MsgBox Err.Description, vbExclamation, "Error in Command21_Click()"
       Resume Exit_Command21_Click
   End Sub

5. The results are contained in tblResults and here they are.
   

   Field1	Field2
   Field2	Tangoo
   Field2	Foxxtrot
   Field1	Braavo
   Field2	Zuluu
   Field1	Dellta

6. Create a Form and set its Record Source to tblResults to actually view the Results.
7. The Code is well commented, but should you have any questions, please feel free to ask.