Required Entries in a Form

[Theman]

I have 5 fields that are required data entry. I have set them up in the Validation Rule property but if a user never clicks the field, the form will close and save the information minus the required fields. What is the best way to ensure that when a record is closed/exited and required fields are not filled in to warn the user that they must fill them in or lose the record? Thanks.

 

[joeyreyma]

i know two approaches to this problem.

1. In your table, you could set the REQUIRED property of all entry-required fields to YES. this would also affect if you use it as recordsource for bound forms.

2. Or, everytime you exit the form, you could loop through each controls and check the value to make sure that it is filled.

eg.
Private Sub Form_Unload(Cancel As Integer)

Dim ctl As Control

On Error GoTo Error_Found
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or _
TypeOf ctl Is ComboBox Or _
TypeOf ctl Is ListBox Then
If IsNull(ctl) Then
If MsgBox("You are about to exit this form with some fields not complete! Do you to exit?", vbYesNo + vbDefaultButton2) = vbYes Then
DoCmd.Close acForm, Me.Name, acSaveNo
Else
ctl.SetFocus
Cancel = True
Exit For
End If
End If
End If
Next

Exit_Here:
Exit Sub

Error_Found:
DoCmd.GoToControl Screen.PreviousControl.Name
GoTo Exit_Here

End Sub

 

[Theman]

Thanks for the reply. I'm getting a compile error/expected expression message at the message box line. I can't seem to correct it. It looks like it might be the reference to the vb dialog box for affirming/cancelling because when I remove it and leave just the message, than there is no error. Any ideas? Thanks. Dan