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
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:
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.