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

 

[AccessVandal via AccessMonster.com]

Why strStudentNumber can't be a primary key, unless this table is not the
main table. Yes, you can remove the primary key and set a unique index.

What's the deal with ">0", the results will always be true if there are
records. Just remove it.

Note that the you are using the form's record source. If the form record
source is filtered, you may get errors.

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)

 

[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)

 

[AccessVandal via AccessMonster.com]

We need more details. what is the form's record source? Is there any records
in the form?

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)

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

[quoted text clipped - 22 lines]

Regards
Kelvan

 

[ThomasK via AccessMonster.com]

Take a look at my post "Finding identical addresses". It's in this same
discussion group. It's similar to what your trying to do.

Tom

 

[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)

 

[AccessVandal via AccessMonster.com]

What was the error message? You cannot insert a new record if the index is
set to unique.
It's better that you post more detail about your tables and relationships.