If you want to stop users from saving records with missing information, make the fields required in the table definition. If you don't want to do that, you need to put your edit code in the form's BeforeUpdate event. That way you can cancel the saving or adding of a record with missing info.
Using the following, paste this into a code module:
Public Function RequiredData(ByVal TheForm As Form) As Boolean 'Check that all TextBox controls have required data entered Dim Ctl As Control Dim Num As Integer On Error GoTo Err_RequiredData RequiredData = False Num = 0 For Each Ctl In TheForm If Ctl.ControlType = acTextBox Then If Ctl = "" Or IsNull(Ctl) Then Num = 1 Exit For End If End If Next Ctl If Num = 1 Then MsgBox "Data is required in " & Ctl.Name & "," & vbCr & _ "please ensure this is entered.", _ vbInformation, "Required Data..." RequiredData = True Else RequiredData = False End If Exit_RequiredData: On Error Resume Next If Not (Ctl Is Nothing) Then Set Ctl = Nothing End If Exit Function Err_RequiredData: Select Case Err Case 0 Resume Next Case Else MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, _ vbInformation End Select End Function
Now to call the code, you can use the following in the Before Update event of the Form:
Private Sub Form_BeforeUpdate(Cancel As Integer) If RequiredData(Me) Then Cancel = -1 End Sub
Now when a user attempts to leave the form, they will be prompted to enter information into the form field specified in the error message as shown in the example below:
Message displayed when user tries to leave form without entering data.