Help with code to prevent Dups.

[Randy]

I have code to prevent duplicate entries placed behind the before update of
a text box. This code works when I have a field in a table with datatype of
text. I am trying this code with a field of data type numbers, but cant get
it to work. Its suposed to alert me when a duplicate employee number "EID"
has been entered. The "EID" field of my table "Emp" is set to "Yes no
duplicates" Here is the code I am using, any help is appreciated...Randy

Private Sub EID_BeforeUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Eid.Value
stLinkCriteria = "[EID]=" & "'" & SID & "'"

'Check Emp table for duplicate Employee ID Number
If DCount("EID", "Emp", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING! Duplicate Employee Number. " _
& SID & " has already been entered." _

'Go to record of original Employee Number

End If

Set rsc = Nothing
End Sub

 

[TomU]

Because your criteria is numeric instead of text, you don't use the single
quotes. Try this:
stLinkCriteria = "[EID]=" & SID

TomU

 

[Wayne Morgan]

You say that you've changed this to a number field. What happens if you
change

stLinkCriteria = "[EID]=" & "'" & SID & "'"

to

stLinkCriteria = "[EID]=" & SID


--
Wayne Morgan
MS Access MVP

I have code to prevent duplicate entries placed behind the before update of
a text box. This code works when I have a field in a table with datatype
of text. I am trying this code with a field of data type numbers, but cant
get it to work. Its suposed to alert me when a duplicate employee number
"EID" has been entered. The "EID" field of my table "Emp" is set to "Yes
no duplicates" Here is the code I am using, any help is
appreciated...Randy

Private Sub EID_BeforeUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Eid.Value
stLinkCriteria = "[EID]=" & "'" & SID & "'"

'Check Emp table for duplicate Employee ID Number
If DCount("EID", "Emp", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING! Duplicate Employee Number. " _
& SID & " has already been entered." _

'Go to record of original Employee Number

End If

Set rsc = Nothing
End Sub