Friday, July 22, 2011

Spell Check MS Access Form Before Updating Table

I got tired of having to spell check after my students entered data into our publications database, so I created a function that spell checks the control values when before the record is saved.

This is the function I added to my BasicFunctions module:

Public Function fnSpellCheck(strTextBox As Control)

     Dim strSpell As String
     'Debug.Print strTextBox
     'This spell check code is from
     'http://www.access-programmers.co.uk/forums/showthread.php?t=130780
     'Posted by forum user "icezebra"
     'Get the value from the text box that is being spell checked.
     strSpell = strTextBox
     'Trying to spell check a null value or zero-length string will result in an error.
     If IsNull(Len(strSpell)) Or Len(strSpell) = 0 Then
     'A null value or zero-length string will skip the spellchecker.
     Else:
     'Define what is to be spell checked.
          With strTextBox
               .SetFocus
               .SelStart = 0
               .SelLength = Len(strSpell)
          End With
          DoCmd.SetWarnings False
          'Run the spellchecker.
          DoCmd.RunCommand acCmdSpelling
          DoCmd.SetWarnings True
          'Note that the line above will turn the warnings back on.
          'If you had the warnings off and want them off comment our or delete the SetWarnings.
     End If

End Function

To use this function I call it in the On Click event of the Save button, before the variables have been assigned values:

     'Running the spell check function on a null or zero-length control value will result in an error.
     If IsNull(me.txtTitle) Then
     'If the value in the control is zero-length the spell checker is skipped.
     Else:
     'Otherwise, the spell checker runs
          Call fnSpellCheck(Forms!frmPamAddition.txtTitle)
     End If
     'Pick up the value for the variable from the form.
     strTitle = me.txtTitle
     If IsNull(me.txtAuthor) Then
     Else:       
          Call fnSpellCheck(Forms!frmPamAddition.txtAuthor)
     End If
     strAuthor = me.txtAuthor
     If IsNull(me.txtOrganization) Then
     Else:         
         Call fnSpellCheck(Forms!frmPamAddition.txtOrganization)
     End If
     strOrganization = me.txtOrganization
     If IsNull(me.txtPublisher) Then
     Else:     
         Call fnSpellCheck(Forms!frmPamAddition.txtPublisher)
     End If
     strPublisher = me.txtPublisher
     If IsNull(me.txtPlaceOfPublication) Then
     Else:        
         Call fnSpellCheck(Forms!frmPamAddition.txtPlaceOfPublication)
     End If
     strPlaceOfPublication = me.txtPlaceOfPublication
     If IsNull(me.txtNote) Then
     Else:
         Call fnSpellCheck(Forms!frmPamAddition.txtNote)
     End If
     strNote = me.txtNote


By listing the controls one after another in this way they will all be spell checked in sequence before the record is saved. After all controls have been checked the rest of the Save button’s On Click event runs.

No comments:

Post a Comment