Event Fires Twice

[Andrew]

I have the usual validation preventing a user from a leaving a field when it
is required. The code is found several places on this great site. It is:

-------------------------------------------------------------------
Private Sub txtName_Exit(Cancel As Integer)
Dim strmsg As String

If Nz(txtName, "") = "" Then
strmsg = "This field is required!"
MsgBox strmsg, vbExclamation, "Uh-Oh!"
Cancel = True
End If
End Sub
-------------------------------------------------------------------

It works. However, if the user starts a record and decides to quit by
hitting escape and then closing the form, the message show twice presumably
because the event fires twice.

When I move the code to a function, the code doesn't fire twice when the
form closes. However, it no longer prevents a user from leaving the Name
field if he tries to move to the next field without entering a value. Here
is my code with the validation moved to a function:

---------------------------------------------------------------------------------

Private Sub txtName_Exit(Cancel As Integer)
Dim bResult As Boolean

Cancel = Messages(bResult)

End Sub

-------------

Public Function Messages(Cancel As Boolean)
Dim strmsg As String

' This tests for null and a blank string

If Nz(txtName, "") = "" Then
strmsg = "This field is required!"
MsgBox strmsg, vbExclamation, "Uh-Oh!"
Cancel = True
End If

End Function

 

[Allen Browne]

Andrew, would you consider another approach? Using the Exit event of the
control will not prevent it being left blank. If the user never visits the
control, the Exit event never fires.

A simple (code-free) solution is to open the table in design view, select
the desired field, and in the lower pane, set its Required property to Yes.
Now Access won't allow the record to be saved if the field is left blank.

If you want to warn the user but not necessarily enforce it, use the
BeforeUpdate event of the *form* (not text box.) Access fires this event
just before the record is saved. Cancel the event, and the data doesn't get
saved.

This kind of thing:

Private Sub Form_BeforeUpate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.txtName) Then
strMsg = "You forgot the name." & vbcrlf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Uh-Oh!") <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End Sub

 

[Andrew]

Thanks Allen. The requestor wanted to stop the user at each field. If you,
who has bascially taught me Access through these posts, don't have a
solution, then I will explain that the validation has to be at the record
level and not the field level.

Thanks, not only for this answer, but the hundreds of others of your answers
I've used.

Andrew, would you consider another approach? Using the Exit event of the
control will not prevent it being left blank. If the user never visits the
control, the Exit event never fires.

A simple (code-free) solution is to open the table in design view, select
the desired field, and in the lower pane, set its Required property to Yes.
Now Access won't allow the record to be saved if the field is left blank.

If you want to warn the user but not necessarily enforce it, use the
BeforeUpdate event of the *form* (not text box.) Access fires this event
just before the record is saved. Cancel the event, and the data doesn't get
saved.

This kind of thing:

Private Sub Form_BeforeUpate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.txtName) Then
strMsg = "You forgot the name." & vbcrlf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Uh-Oh!") <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End Sub