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

Validating for Missing Data

Validating Missing Data by Responding to Data Entry Events:

We can use various techniques for validating data being entered into our database, as seen in the Validating Data in Microsoft Access article, however sometimes our validation routines can be overly restrictive.

There may be occasions where we need to be more flexible in our approach, and we may just want to warn the users of the database that data is missing when they are entering a record into the form. When we are entering the data, it is possible to respond the the data entry event, and use a Microsoft Access MsgBox function to perform different actions depending on what the database user wants to do.

Consider that we have the following Microsoft Access form:

Microsoft Access form that we are going to validate for missing information
Microsoft Access form that we are going to validate for missing information

In the BeforeUpdate event procedure, we would like to enter our code that will check for user entry into the [BirthDate] field and inform the user if the information is missing before allowing the save to take place. The VBA used to perform this action is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'-----------------------------------------------
'Check to see if DateofBirth is entered into
'the form. Ask the user before saving the record
'-----------------------------------------------

    Dim strMessage As String
    Dim intOptions As Integer
    Dim strTitle As String
    Dim bytChoice As Byte

    If IsNull(BirthDate) Then
        strMessage = "You have not entered a Birth Date for this Employee." & _
        "Do you still wish to Save the record?" & vbCrLf & vbCrLf & _
        "Click Cancel to return to the record."
        intOptions = vbQuestion + vbOKCancel
        strTitle = "Missing Data..."
        bytChoice = MsgBox(strMessage, intOptions, strTitle)

        If bytChoice = vbCancel Then
            'Set focus to the BirthDate field
            BirthDate.SetFocus
            'Cancel saving the record
            Cancel = True
        End If
    End If
End Sub

Now, when we attempt to save a record without entering data into the BirthDate field, we are prompted with a MsgBox function. The message box gives us two options of what we would like to do if we have not entered all of the information required into the Microsoft Access form. We have the option to OK the action, which will save the record with the missing data.

If we Cancel the Save, we are returned to the record, and focus is set to the field that is missing the data:

Information given in the MsgBox if we have missing data.
Information given in the MsgBox if we have missing data.

This gives us another option of data validation, and is one of the techniques that can be used in your database application.