Help with code to prevent Dups.

R

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
 
T

TomU

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

TomU
 
W

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


Randy said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top