Problems Managing Required Field and Form Events

[George Atkins]

Acc2007. I have a single-record form with a field that must be filled in
before closing, but only if the record is edited. I did not use the
"Required" field property, but am setting it up on the form, via events. The
form is modal, with a command button to close the form. In most cases, my
coding works properly; however, the user can right-click on the form and
choose Close. This is where things fall apart. But first, the regular code:

Code:

Private Function CheckForNoReferral() As String ' routine to check the
field, called by events
If Me.Dirty Then
If IsNull(Me.Referral_Source) Then
MsgBox "You must select a referral source!", vbCritical
CheckForNoReferral = "Missing"
Exit Function
Else
End If
End If
CheckForNoReferral = "OK"
End Function
-----------------------
' Here is the event for the Close button...It calls the function, above.
Private Sub cmdCloseForm_Click()
Dim CheckVal As String
CheckVal = CheckForNoReferral
If CheckVal = "Missing" Then
Me.Referral_Source.SetFocus
Exit Sub
Else
DoCmd.Close
End If
' Call Form_Close
End Sub
----------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CheckVal As String
CheckVal = CheckForNoReferral
If CheckVal = "Missing" Then
Me.Referral_Source.SetFocus
Cancel = True
End If
End Sub
--------------------------



Now, if the user edits any field and clicks the Close button, the
CheckForNoReferral function does it job correctly. If I right-click on the
form and choose Close, it still runs the BeforeUpdate event, but then
displays the following message: "You can't save this record at this time.
Microsoft Office Access may have encountered an error while trying to save a
record. If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway? Yes/No."

I tried to write code to the form's close event, using the same code as the
cmdCloseForm_Click routine. This accomplishes nothing. So the issue is how do
I prevent the "You can't save this record" message from occurring? Thanks for
your consideration and any advise.

George