How do I get a message box to appear when duplicate data is entered?

[Ryker7]

I am entering information on a form and the primary key is social security number. If a duplicate social security number is entered, I want a message box to appear stating the fact. Social security number is indexed so Access does give a message of duplicate data, but it causes any macro running to crash and you have to go through a series of boxes to close. To someone not used to Access, this can be frightening and I want this as user friendly as possible so therefore, I need a simple message box instead of the system message. Any suggestions?

 

[jfgambit]

Add the following to the After Update Event of the SS#
Docmd.SetWarning False

Dim rsSS as Variant
DLookUp("[SSNbr]","tblEmployees","[SSNbr] = '" & Me.SSNbr & "'"
If IsNull(rsSS) or rsSS = "" then
Else
MsgBox "Duplicate SS# this record cannot be added",vbokonly
me.SSNbr = ""
End if

Docmd.SetWarnings True


You will need to change the names as needed for your tables and fields:
DLookUp("[NameofField]","Table Name","[NameofField] = '" & Me.FormfieldName & "'"

HTH


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[0011268899]

I have tried using the same code as above for the same reasons as ryker7, but I keep on getting a syntax error. Maybe I am just tired, but I've looked at the code for at least 30 minutes now and I've played around with it, but still can't seem to find anything wrong with it. Can someone tell me what I am doing wrong? Thanks in advance.


Private Sub SSN_AfterUpdate()
DoCmd.SetWarnings False

Dim rsSS As Variant
DLookUp("[SSN]","OpenItems","[SSN] = '" & Me.SSN & "'&quot
If IsNull(rsSS) Or rsSS = "" Then
Else
MsgBox "Duplicate SS# this record cannot be added", vbOKOnly
Me.SSN = ""
End If

DoCmd.SetWarnings True


End Sub