Preventing Duplicates from being entered

[Benedikt]

Hi

I am trying to prevent duplicates being entered in my form and if someone
enters number that is already in the table Projects is should go to that
record.

Do you now if this code works only if the field (strStudentNumber) is
PrimaryKey? Is it possible to use this code without PrimaryKey?


Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strStudentNumber.Value
stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"


If DCount("strStudentNumber", "Projects", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Reg Number " & SID & " has already been entered." & vbCr &
vbCr & "", vbInformation, "Duplicate Information"
'Go to record of original Reg Number
rsc.FindNext stLinkCriteria
Me.Bookmark = rsc.Bookmark


End If

Set rsc = Nothing

End Sub


Many thanks.
Benedikt F.
(e-mail address removed)

 

[Lord Kelvan]

it would be eaiser to set up a unique constraint on the table

to do this open the table in design view

click on view in the menu bar then click on indexes

type in a name for the unique constraint in the index name box
select the sid field in the field name
and then down the bottom of that wind you will see

primary no
unique no
ingore nulls no

change it to

primary no
unique yes
ingore nulls no

and that should do it

hope this helps

Regards
Kelvan

 

[Benedikt]

Hi again



I am trying to get this to work without any luck. I always get this error.



run-time error 3420 object invalid or no longer set (Me.Bookmark = rsc.Bookmark)



I did change my field to unique



I am using Access 2007 if that changes anything



best regards,

Benedikt F.

(e-mail address removed)

 

[Benedikt]

Hi again



I downloaded the Project database from Microsoft website and I am trying to
customize that database for my customer. I added one field called
"strStudentNumber" to table Project and that is the record source for my
form. In this database is form called Project list where I can press button
called New Project. This button filters and creates new Project record and
when I enter some duplicated record in my form it will fire the error
message.



I hope this will help



best regards
Benedikt F.
(e-mail address removed)