Adding Spell Checker to Existing VBA

[StuartG]

Hello all,

I am attempting to add a spell checker code which was taken from the forum to my existing VBA form that was completed for me but I am a little stuck.
Below is the code that was written for me.
What I added is a different color.

Is there a way that when I click the save comment it will then initiate the spell check, and after its complete it saves the comment, or is it better to have a button on my form labelled "Spell Check"

Option Compare Database
Option Explicit

Private Sub cmdCancel_Click()
'close without saving
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdSave_Click()

If Nz(Me.Comments, "") = "" Then
MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
Me.Comments.SetFocus
Exit Sub
End If

'append new comment to tblMemoFieldVH
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime)" & _
" VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);"

DoCmd.Close acForm, Me.Name

'refresh comments subform
Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery
End Sub

Private Sub Form_frmNewComment_Exit(Cancel As Integer)
Dim strSpell
strSpell = frmNewcomment
If IsNull(Len(strSpell)) Or Len(strSpell) = 0 Then
Exit Sub
End If
With frmNewcomment
.SetFocus
.SelStart = 0
.SelLength = Len(strSpell)
End With
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True

End Sub


Private Sub Form_Load()
'Use ID from main form
Me.ID = Forms!frmUpdateForm.ID
End Sub

 

[cheekybuddha]

Hi,

Can you clarify whether frmNewcomment is a form or a textbox?

I'd imagine you'd want to call the spellcheck on the contents of a textbox.

You can add a generic function to a standard module like:

Function SpellChecker(txt As TextBox) As Boolean
On Error GoTo Err_SpellChecker

  With txt
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    SpellChecker = Len(.Text) > 0
  End With
  DoCmd.RunCommand acCmdSpelling

Exit_SpellChecker:
  Exit Function

Err_SpellChecker:
  Select Case Err.Number
  Case 2046
    ' not available
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: SpellChecker" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basTest", , "Error: " & Err.Number
  End Select
  Resume Exit_SpellChecker
 
End Function

Then, in your Save sub do something like:

Private Sub cmdSave_Click()

  If Nz(Me.Comments, "") = "" Then
    MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
    Me.Comments.SetFocus
    Exit Sub
  Else
    Call SpellChecker(Me.Comments)
  End If

' append new comment to tblMemoFieldVH
  CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime)" & _
                    " VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);"

  DoCmd.Close acForm, Me.Name

' refresh comments subform
  Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

hthm

d

 

[StuartG]

My form looks like this.
It looks to me like a text box in a form?

The table field for this is a "Long Text" field.

 

[cheekybuddha]

Did you try the code I suggested?

 

[StuartG]

Did you try the code I suggested?

Hi There
No not yet as not sure where I should put in within my code?

 

[cheekybuddha]

OK, First create a new standard module.

Name it 'modSpellCheck'

Copy the function SpellChecker from post #2 in to it.

Then, in your form's module, find the cmdSave_Click() sub, and substitute what you have with the code in post #2 (or just add the Else portion to the If statement)

hth,

d

 

[StuartG]

OK, so I did that part and the spell checker runs, however it has messed something up.
When I used to open the form to make a new comment it would always open with the ID number that I had on a previous form, picture below:

The code previously always had that ID number filled in?

What did I do wrong?

 

[cheekybuddha]

Did you overwrite the Load sub that you had previously?

Private Sub Form_Load()
  'Use ID from main form
  Me.ID = Forms!frmUpdateForm.ID
End Sub

If so, add it back in.

 

[StuartG]

Further to my last post it has also stopped the "cancel" button working in the "new comment entry"

 

[StuartG]

Fixed it..
Sorry I am really new to under VBA code..

 

[isladogs]

@cheekybuddha
Hi David. Good to see you here for a change & providing your usual expert help.
Hope to see more of you in the future

@StuartG
As I have a copy of that form, just to say I can confirm it works if you do exactly what was suggested in post #6

 

[StuartG]

@cheekybuddha,
I echo the comments from isla, thank you

@isladogs,
Yep, i can confirm it all works as previously but now with the added spell checker function.. (That will stop complaints from my colleague )

 

[cheekybuddha]

@Colin - always nice to drop in; a quiet day over in the other building!

@StuartG -

d