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)
            '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)
            '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)
            '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       
            '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.


  1. Hi, Does this work the same way for Submit button click?

  2. I'm not sure what you mean by a Submit button. I use unbound forms and create a Save button on the form. The above code is then included in the On_Click event of the Save button. If you can insert this code into the code that fires when you click your Submit button it should work.

  3. Hi, Thank you for the response. The form I have is for SharePoint linked access database. So Fields in the form are bound to the columns in the access database. Once the user fills in all data using the combo boxes and text fields, they can hit the submit button which will push the form data to the DB. However, if some of the fields which are supposed to be field are left blank by users, there should be a pop up to notify the user of the mandatory fields. Your code above (bless you for sharing it with the rest of the world!!) seems to work well for a unbound form but does not react to bound fields. Since I'm very new (2 weeks old) to MS access i tried my excel vba to it and failed to accomplish. could you perhaps guide me on this?

  4. Hi Bloggerking, that is one of the reasons I use unbound forms almost exclusively. It does require more coding, but I can better control what happens with the data. I have never used SharePoint, nor have I ever used VBA in Excel, so I can't really help you there. Sorry, I wish I could be more help.