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.

Changing List Box Results in MS Access

Our publications database currently has about 81,000 items listed in it, mostly union and company grey literature. The database opens to a search form which contains a list box. This used to load with all the items when opened, but once we got past 60,000 it was slow to load. Since there is no need for all items to load I looked for ways to limit the list box results. There is probably a way to load 100 random records, but I wasn’t able to figure one out so instead it started with the first 100 records and each time the database is opened or the form refreshed it opens the next 100 in sequence.

To control where the record set begins I added a new table to the database, tblLoad. There are only two fields and one record in the table. The ID field has a value of 1 and never changes, while the Start field contains the lower limit for the list box record set and is initially had a value of 1 but the value is increased by 99 whenever the search form opens or is refreshed.

Here is the VBA code for the search form’s On Open event:

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

     Dim strSQL As String           'This is the SQL query that provides the record set for the list box.

     Dim lngStart As Long           'This establishes the lower limit of the record set.

     Dim lngEnd As Long            'This establishes the upper limit of the record set.

     Dim lngMax As Long           'This is the ID for the last record in tblPams.

     'Pick up the lower limit from tblLoad.

     lngStart = DLookup("[Start]", "tblLoad", "[ID] = 1")

     'Debug.Print "lngStart = " & lngStart

     'Create the upper limit by adding 99 to the lower limit.

     lngEnd = lngStart + 99

     'Debug.Print "lngEnd = " & lngEnd

     'Pick up the ID for the last record in tblPams.

     lngMax = DMax("[PamID]", "tblPams")

     'Debug.Print "lngMax = " & lngMax

     'To avoid generating a error when the SQL query is run

     'test to make sure that lngEnd is not greater than lngMax.

     If lngEnd > lngMax Then

     'If lngEnd is greater than lngMax reset lngEnd to lngMax.

          lngEnd = lngMax

     Else:

     'Otherwise, keep lngEnd the same.

          lngEnd = lngEnd

     End If

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams "

     strSQL = strSQL & "WHERE (tblPams.PamID BETWEEN " & lngStart & " AND " & lngEnd & ") "

     strSQL = strSQL & "ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     Me.lstPamList.RowSource = strSQL

     'If the last record in the table has been reached we need to reset the lower limit to 1.

     If lngEnd = lngMax Then

          DoCmd.RunSQL "Update tblLoad SET tblLoad.Start = 1 Where tblLoad.ID = 1"

     Else:

     'Otherwise, the current upper limit becomes the next lower limit.

          DoCmd.RunSQL "UPDATE tblLoad SET tblLoad.Start = " & lngEnd & " Where tblLoad.ID = 1"

     End If

DoCmd.SetWarnings True

End Sub

Next and Previous Buttons on a MS Access Form

Since I generally use unbound controls on my forms when a record is selected from a listbox the built-in navigation buttons do not work, the form only sees one record. In order to allow users to navigate back and forth through the list in the listbox I needed to create my own navigation buttons.

The key to creating Next Record and Previous Record buttons is to have a public variable to hold the list index value.

     Option Compare Database

     Public intIndex As Integer

This variable is initially populated in the form’s On Load event.

     intIndex = Forms!frmFolderItemList.lstItems.ListIndex

The On Click event for the Next Record button takes intIndex, adds 1 to it and moves the focus of the listbox to that List Index value. Then the On Load event is called.

     Private Sub cmdNext_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex + 1

          'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

         Call Form_Load

     End Sub

The On Click event for the Previous Record button subtracts 1.

     Private Sub cmdPrevious_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex - 1

         'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

          Call Form_Load

     End Sub

This works very well, except when the user tries to click the Next Record button when the last record is already being displayed, or the Previous Record button when the first record is being displayed. To fix this the Previous Record button needs to be hidden when the listbox focus is on the first record, and the Next Record button needs to be hidden when the focus is on the last record. You can determine where the focus is in the list by comparing the List Index to the List Row Count.

Dimension a variable for the List Row Count in the On Load event for the form and populate it.

     Dim intRowCount As Integer

     intRowCount = Forms!frmFolderItemList.lstItems.ListCount

The variable for the List Index, intIndex, is a public variable that was populated earlier in the On Load event in order to be available for the On Click events of the Next Record and Previous Record buttons. The variable is simply reused here.

     'You cannot hide a control that has focus, so make sure the focus goes to some

     'control other than the Next Record or Previous Record buttons.

     Me.txtDescription.SetFocus

     If intRowCount > 1 Then

     'For a list with at least two items the Next button will be used

     'as long as you are not already at the last item.

          If intIndex = intRowCount - 2 Then

          'The List Index starts at 0 and the Row Count starts at 1 and counts the column heads.

          'If you are using the column heads you need to subtract 2 from the Row Count in order

          'to identify the last item in the list and hide the Next button.

          'If you are not using the column heads you need to subtract only 1.

               cmdNext.Visible = False

          Else:

               cmdNext.Visible = True

          End If

     Else:

     'When there is only one item in the list the Next button is not used.

          cmdNext.Visible = False

     End If

     If intRowCount > 1 Then

     'For a list with at least two items the Previous button will be used

     'as long as you are not already at the first item.

          If intIndex = 0 Then

          'The first item in the list will always have a List Index value of 0,

          'so if that is true hide the Previous button.

               cmdPrevious.Visible = False

          Else:

               cmdPrevious.Visible = True

          End If

     Else:

     'When there is only one item in the list the Previous button is not used.

          cmdPrevious.Visible = False

     End If