Help with error message/checking

[Paul B.]

I have the following code behind the OnExit event of my textbox
[EpistryNumber]. The first part checks to see if the number exists, and if
so, it is supposed to warn the user to enter a different number and then
clear the entry. What is happening though is my MsgBox comes up, and after
acknowledging it, I get another error message saying "The value in the field
or record violates the validation rule for the record or field". I don't have
any validation rules except for not allowing duplicates. I thought my code
would prevent the user from entering a duplicate.

What I am doing wrong? I would like only my message box(es) to appear either
way and if it is a duplicate, clear the entry and have the user try again.

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

Private Sub EpistryNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_EpistryNumber_BeforeUpdate

If Not IsNull(DLookup("EpistryNumber", "tblEpistry", "EpistryNumber ='" &_
EpistryNumber & "' ")) Then

Call MsgBox("The Epistry Number you entered already exists." & vbCrLf &
"" & _ vbCrLf & "Please check the number and try again." & vbCrLf & "" &
vbCrLf & "", _ vbOKOnly + vbExclamation + vbSystemModal + vbDefaultButton1,
"Error....please _ try again")
Me.EpistryNumber.Undo
Cancel = True
Exit Sub

Else

Call MsgBox("Unique Epistry Number has been accepted." & vbCrLf & "" &_
vbCrLf & "", vbOKOnly + vbInformation + vbSystemModal +_ vbDefaultButton1,
"Epistry number accepted")

End If

Exit_EpistryNumber_BeforeUpdate:
Exit Sub

Err_EpistryNumber_BeforeUpdate:
Call MsgBox("There has been an error." & vbCrLf & "" & vbCrLf & "",
vbOKOnly_
+ vbCritical + vbSystemModal + vbDefaultButton1, "Error....please try again")
Resume Exit_EpistryNumber_BeforeUpdate

End Sub

 

[strive4peace]

Hi Paul,

try this:

If nz(DLookup("EpistryNumber",
"tblEpistry",
"EpistryNumber ='" &_
me.EpistryNumber & "' AND RecordID <>" & me.RecordID),"") <>
"" Then

where
RecordID = the name of your primary key (assuming it is a
number)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com