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

 

[Pat Hartman]

joeyreyma,
FYI - the Unload event is WAY too late to do this type of edit. The update has already taken place so noting you do at this point can affect it. You need to do the edits in the BeforeUpdate event of the form. That way, if the edits fail, you can cancel the update and prevent the bad or missing data from being saved.

To prove to yourself that the ONLY event that works for this purpose is the BeforeUpdate event, put messageboxes in various events and check the table as each one pops up. For example, the record should show the original values (or not be there in the case of an insert) in the BeforeUpdate event but show the update or new record in the AfterUpdate event which fires immediately after the table update.