Default Duplicate Record Message

[Lori]

Hi,
For some reason the the default messages when closing a record that comes up
for required fields when null, is not working anymore. I was told that
sometimes default functions in access like this stop working sometimes. So I
added the code below. Now when you enter a duplicate in the required field,
no duplicates allowed. It still does not allow for duplicates, but the
default message does not pop up, just like the required field message. It
just does not save the record.

Can someone tell me what I would need to add to the below to include no
duplicates or should I add it under a different event other than "on click"
on the command button to close the record.

If IsNull(Me![ContactCompanyID]) Then
If MsgBox("'File Number' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "File Number is a required field") = vbCancel Then
DoCmd.Close
Else
Me![ContactCompanyID].SetFocus
End If
Else
If IsNull(Me![UpdatedOn]) Then
If MsgBox("'Updated On' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "Updated On is a required field") = vbCancel Then
DoCmd.Close
Else
Me![UpdatedOn].SetFocus
End If
Else
If IsNull(Me![UpdatedBy]) Then
If MsgBox("'Updated By' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "Updated By is a required field") = vbCancel Then
DoCmd.Close
Else
Me![UpdatedBy].SetFocus
End If
Else
DoCmd.Close
End If
End If
End If

 

[Allen Browne]

Lori, you are in a very long line of users who continue to get hurt by this
bug in Access. As you found, it just silently discards you data with no
warning when you execute the Close action or method. More info in this
article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

Assuming this is a bound form, you should place your validation checks in
the BeforeUpdate event of the *form*.

Then in the Click event of your of your command button, explicitly save
before you Close. This triggers a trappable error if the record cannot be
saved, so the Close never executes, and so you have worked around the bug.

Private Sub cmdClose_Click()
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name
End Sub