Using VBA to require certain fields be filled in on an unbound form in MS Access.

On some of the data entry forms that I use there are fields (text box controls) that are required and others that are optional. Since I normally use unbound forms I can easily identify blank required fields and remind the user to fill them in.

On this form has three controls that are for required fields:

If the user leaves the Collection Number, the Scope/Content Note, or the Languages blank I need the code to identify the blank required fields and prompt the user to filled them in. This is pretty easily done by using the function Nz() to convert the null values from the required fields to “DataError” and the optional ones to a zero-length string.

In the On_Click event of the Save button (cmdSave_Click), after dimensioning the variables I pick up the values from the controls and use “DataError” to isolate where the user needs to add data:

    strCollNum = Nz(Me.txtCollNum, "DataError")

    strTitle = Nz(Me.txtTitle, "")

    strScopeContent = Nz(Me.txtScopeContent, "DataError")

    strType = Nz(Me.txtType, "")

    strFormat = Nz(Me.txtFormat, "")

    strExtent = Nz(Me.txtExtent, "")

    strDate = Nz(Me.txtDate, "")

    strLocation = Nz(Me.txtLocation, "")

    strRestrictions = Nz(Me.txtRestrictions, "")

    strRelation = Nz(Me.txtRelation, "")

    strLanguage = Nz(Me.txtLanguage, " DataError ")

    strSubjects = Nz(Me.txtSubjects, "")

    strKeywords = Nz(Me.txtKeywords, "")

    strNotes = Nz(Me.txtNotes, "")

        'Put the three required fields together in one string, strError.

        strError = strCollNum & strDesc & strLanguages

        'Test the strError to see if it contains DataError.

        If strError Like "*DataError*" Then

        'If strError contains DataError test each required field.

            If strCollNum Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtCollNum.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

                Me.txtCollNum.BackColor = RGB(199, 208, 227)

            End If

            If strDesc Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtDescription.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

                Me.txtDescription.BackColor = RGB(199, 208, 227)

            End If

            If strLanguages Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtLanguage.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

               Me.txtLanguage.BackColor = RGB(199, 208, 227)

            End If

            'Prompt the user to fill in the missing date and exit the sub.

            MsgBox "Red fields are required. Please complete form and re-save.", vbCritical

            Exit Sub       

       Else:

            'The code to write the data to the tables would go here to execute when all three 

             'required fields contain data.

      End If

Here is what the form looked like when I tried to save it after entering a collection number without a Scope/Content Note or Language.

Since the sub was exited after the message box was presented the user will be returned to the form when the message box is closed: the record cannot be saved unless the required fields contain data. Whether or not they contain valid data is another question, but using a similar set of tests the data can be validated and rejected if not valid.

Next and Previous Buttons for MS Access Bound Forms

Normally I prefer to use unbound controls on my forms since I feel it gives me more flexibility and control, however on a recent project I needed to use bound controls. This form has 13 subforms in a tab control for 13 different audio-visual media formats. I couldn’t allow the users to create new records using the navigation buttons on the subforms since that would create records in the table with no way of linking them back to the proper subform. I, instead, added a “New Item” button to each subform, which creates the new record and inserts the media format into it, and deleted the navigation buttons. This meant I needed Next and Previous buttons on the subforms.

Back on 07Ju11 I explained how to make Next and Previous buttons on an unbound form. It’s a little different on a bound form. I did, however, want some of the same behavior. I don’t like the Previous button to show if you are on the first record or the Next button to show if you are on the last record. This prevents the user from click a button when there is nowhere to go. The code below is how I control the display of the buttons. I ended up including Previous, Next, and Last buttons, a First button could also be included if you like. I also included a text box to let the user know that they are on “record 1 of 7”, or wherever they are.

For the sake of simplicity I used the exact same code for all three buttons:

     Private Sub cmdPrevious _Click()

     'Private Sub cmdNext _Click()

     'Private Sub cmdLast _Click()

          Dim intIndex As Integer

          Dim intCount As Integer

          'For the Previous button use:

          DoCmd.GoToRecord , , acPrevious

          'For the Next button use:

          'DoCmd.GoToRecord , , acNext

          'For the Last button use:

          'DoCmd.GoToRecord , , acLast

          'Get the count of records in the form's record set.

          intCount = Me.Recordset.RecordCount

          'Debug.Print " Count = " & intCount

          'Get the cursor position in the recordset.

          'Since the cursor position for the first record is zero

          'add 1 to the cursor position so that the last one will equal the record count.

          intIndex = Me.Recordset.AbsolutePosition + 1

          'Debug.Print "Postion = " & intIndex

          If intCount = intIndex Then

          'The last record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Next and Last buttons, one which has the focus,

          'make sure the Previous button is visible and move the focus to it.

               Me.cmdPrevious.Visible = True

               Me.cmdPrevious.SetFocus

               Me.cmdNext.Visible = False

               Me.cmdLast.Visible = False

          ElseIf intIndex = 1 Then

          'The first record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Previous button, which has the focus,

          'make sure the Next button is visible and move the focus to it.

               Me.cmdNext.Visible = True

               Me.cmdNext.SetFocus

               Me.cmdPrevious.Visible = False

               Me.cmdLast.Visible = True

          ElseIf intIndex > 1 And intIndex < intCount Then

          'The displayed record is not the first or last record, all buttons should show.

          'Since you are not changing the visible status of the button used 

          'the focus does not need to be reset.

               Me.cmdPrevious.Visible = True

               Me.cmdNext.Visible = True

               Me.cmdLast.Visible = True

          End If

         'Give the user an indication of where they are in the record set.

          Me.txtItemCount = "Item " & intIndex & " of " & intCount

     End Sub

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.

Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers

    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows

    ‘for users greater than level 1.

    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only

    ‘users greater than level 2.

    ‘A level 4 control has code to allow only users greater than level 3.         

    If intUser > 3 Then

        ‘This is where you would put the code for your event procedure.

    Else:

        MsgBox ("Sorry, you are not authorized to access this procedure.")

    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then

        MsgBox ("Sorry, you are not authorized to edit the Collection Title")

        Me.DonorsName.SetFocus

    End If

Next Week: Back-End Security.