Thursday, April 4, 2013

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.