if you have many fields that are required you can also use a public function for validation using the tag property of the controls.
This is similar to Vlad's code but a lot less typing if there are many controls to validate. Additionally it will concatenate a string of the controls labels
and fire a messagebox. It will also color the border of the control red.
In a standard module put the following code:
Code:
Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
'validated controls must have a label. Ok to use a hidden label if needed.
Dim ctl As control
Dim flg As Boolean
Dim strOut As String
'flg = True
For Each ctl In frm.Controls
If InStr(1, ctl.Tag, TagCharacter) Then 'using Instr() allows for multiple tags
If Nz(ctl.Value, "") = "" Then
flg = True
ctl.BorderColor = vbRed
strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
Else
ctl.BorderColor = vbBlack
End If
End If
Next
If flg = True Then
MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
End If
ValidateForm = flg
End Function
On your forms:
In the tag property of the control you enter a value such as VD8 then in the before update of the form enter
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = ValidateForm(Me, "VD8")
End Sub
Personally I tend to use it in the close form command button on my forms
Code:
Private Sub cmdCloseForm_Click()
If ValidateForm(Me, "VD8") = True Then
Exit Sub
End If
DoCmd.Close acForm, Me.Name
End Sub
HTH