databasedev.co.uk - database solutions and downloads for microsoft access

Validating Required Data

Validating Required Data in Microsoft Access Text boxes:

You may want to prevent users from saving Microsoft Access records with missing information, especially if this data is important. There are several approaches to this and various methods can be used in Microsoft Access, which can be applied in various places. Ideally, you will not be letting your Microsoft Access database users have access to your database tables, so you will need to validate this information in your database forms. You will need to put your validating code in the Microsoft Access form's BeforeUpdate event. This way you can cancel the saving or adding of a record with missing info.

In the BeforeUpdate event procedure of the Microsoft Access form that you want to validate the data on for missing information, we can paste in the following code sample:

Private Sub Form_BeforeUpdate(Cancel As Integer)


'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control


    nl = vbNewLine & vbNewLine

    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
               "You can't save this record until this data is provided!" & nl & _
               "Enter the data and try again . . . "
          Style = vbCritical + vbOKOnly
          Title = "Required Data..."
          MsgBox msg, Style, Title
          ctl.SetFocus
          Cancel = True
          Exit For
        End If
      End If
    Next

End Sub

Now in the Microsoft Access form, and for the property of each text box we would like to validate against being empty, we need to type in an asterisk (*) into the Tag property.

For this we need to select the text box and from the Property window, choose the Other tab and type into the Tag property:

Setting the Tag property of a text box to allow validation
Setting the Tag property of a text box to allow validation

Repeat this process for any further fields that you need to prevent missing data from.

When we enter information into the form, any fields that are tagged and left empty, will cause the following error message to be generated, and will not allow updating of the record:

Warning message when data entry is validated.
The above shows the record trying to be saved when the 'BirthDate' field has been left empty, and has been set to not allow missing information.