Problem with code to catch duplicate Primay Key

[Tim]

Access 2003
I have a Contacts form the with the Contacts table as the record source.
The field Initials is entered by the user and is the primary key for the
table.
The Before Update for this field has code to check for duplicates, fire a
message box if a duplicate is found, and go to the record with those initials.

The problem I'm having is that the code works sometimes but not always (when
it does not work, the generic Access message about not allowing duplicate
primary key is shown before thee record is saved- not helpful to the user) -
see below for code.

As an example, records with the Initials AK and AB exist. If I try to enter
a new record with the initials AK, the code fires and I get the message and
taken to the existing record. If I enter AB into a new record, I can leave
the Initials field without the code firing - I cannot create a new record and
get the Access message. Of the 17 records already present, 7 of the initials
cause the code to execute, but the other 10 do not.

Is any one able to see what I am missing?
Thank you

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique set of
Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

 

[Mike Painter]

Try trimming the string and checking to see if there are trailing or
leading spaces in the existing strings.
It is possible to introduce leading spaces.
I never use the .value property and Me.Initials will work fine.

That said, using initials as a key, especially if they are names, is never a
good idea. (I once worked in a store with two David Leroy Smiths.)
Since you will have to use a combo or list box to lookup a particular
record, an index on the lask name will allow picking between Mike Painter
and Mitch Palmer with ease.

"The Text property returns the formatted string. The Text property may be
different than the Value property for a text box control. The Text property
is the current contents of the control. The Value property is the saved
value of the text box control. The Text property is always current while the
control has the focus."
http://msdn.microsoft.com/en-us/library/aa207379(office.10).aspx

Access 2003
I have a Contacts form the with the Contacts table as the record
source.
The field Initials is entered by the user and is the primary key for
the table.
The Before Update for this field has code to check for duplicates,
fire a message box if a duplicate is found, and go to the record with
those initials.

The problem I'm having is that the code works sometimes but not
always (when it does not work, the generic Access message about not
allowing duplicate primary key is shown before thee record is saved-
not helpful to the user) - see below for code.

As an example, records with the Initials AK and AB exist. If I try
to enter a new record with the initials AK, the code fires and I get
the message and taken to the existing record. If I enter AB into a
new record, I can leave the Initials field without the code firing -
I cannot create a new record and get the Access message. Of the 17
records already present, 7 of the initials cause the code to execute,
but the other 10 do not.

Is any one able to see what I am missing?
Thank you

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) > 0
Then 'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

 

[Tim]

Thanks Mike,

I agree about Initials used as a key, but it is the database I was given to
work on. I'll see if I can alter how it is set up.

However, I would still like to figure out why the code is not working.

For the AB initial, I retyped it into the db making sure there were no
spaces, and it still passes through the code when trying to create a new
record that also has AB.

For the code, I changed:
SID = Me.Initials.Value
TO
SID = Trim(me.Initials)
and

stLinkCriteria = "[Initials]=" & "'" & SID & "'"
TO
stLinkCriteria = Trim("[Initials]=") & "'" & SID & "'"
- not sure I got the trim correct here

With the changed code, some existing initials pass through the code and some
don't. Any other thoughts?

thanks,
tim

Try trimming the string and checking to see if there are trailing or
leading spaces in the existing strings.
It is possible to introduce leading spaces.
I never use the .value property and Me.Initials will work fine.

That said, using initials as a key, especially if they are names, is never a
good idea. (I once worked in a store with two David Leroy Smiths.)
Since you will have to use a combo or list box to lookup a particular
record, an index on the lask name will allow picking between Mike Painter
and Mitch Palmer with ease.

"The Text property returns the formatted string. The Text property may be
different than the Value property for a text box control. The Text property
is the current contents of the control. The Value property is the saved
value of the text box control. The Text property is always current while the
control has the focus."
http://msdn.microsoft.com/en-us/library/aa207379(office.10).aspx

Access 2003
I have a Contacts form the with the Contacts table as the record
source.
The field Initials is entered by the user and is the primary key for
the table.
The Before Update for this field has code to check for duplicates,
fire a message box if a duplicate is found, and go to the record with
those initials.

The problem I'm having is that the code works sometimes but not
always (when it does not work, the generic Access message about not
allowing duplicate primary key is shown before thee record is saved-
not helpful to the user) - see below for code.

As an example, records with the Initials AK and AB exist. If I try
to enter a new record with the initials AK, the code fires and I get
the message and taken to the existing record. If I enter AB into a
new record, I can leave the Initials field without the code firing -
I cannot create a new record and get the Access message. Of the 17
records already present, 7 of the initials cause the code to execute,
but the other 10 do not.

Is any one able to see what I am missing?
Thank you

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) > 0
Then 'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

.

 

[Mike Painter]

Give the brownies more milk.

You might try getting rid of the dcount and see if rsc.FindFirst
stLinkCriteria
finds anything.
If it does you can put up your message there.

Thanks Mike,

I agree about Initials used as a key, but it is the database I was
given to work on. I'll see if I can alter how it is set up.

However, I would still like to figure out why the code is not working.

For the AB initial, I retyped it into the db making sure there were no
spaces, and it still passes through the code when trying to create a
new record that also has AB.

For the code, I changed:
SID = Me.Initials.Value
TO
SID = Trim(me.Initials)
and

stLinkCriteria = "[Initials]=" & "'" & SID & "'"
TO
stLinkCriteria = Trim("[Initials]=") & "'" & SID & "'"
- not sure I got the trim correct here

With the changed code, some existing initials pass through the code
and some don't. Any other thoughts?

thanks,
tim

Try trimming the string and checking to see if there are trailing or
leading spaces in the existing strings.
It is possible to introduce leading spaces.
I never use the .value property and Me.Initials will work fine.

That said, using initials as a key, especially if they are names, is
never a good idea. (I once worked in a store with two David Leroy
Smiths.)
Since you will have to use a combo or list box to lookup a particular
record, an index on the lask name will allow picking between Mike
Painter and Mitch Palmer with ease.

"The Text property returns the formatted string. The Text property
may be different than the Value property for a text box control. The
Text property is the current contents of the control. The Value
property is the saved value of the text box control. The Text
property is always current while the control has the focus."
http://msdn.microsoft.com/en-us/library/aa207379(office.10).aspx

Access 2003
I have a Contacts form the with the Contacts table as the record
source.
The field Initials is entered by the user and is the primary key for
the table.
The Before Update for this field has code to check for duplicates,
fire a message box if a duplicate is found, and go to the record
with those initials.

The problem I'm having is that the code works sometimes but not
always (when it does not work, the generic Access message about not
allowing duplicate primary key is shown before thee record is saved-
not helpful to the user) - see below for code.

As an example, records with the Initials AK and AB exist. If I try
to enter a new record with the initials AK, the code fires and I get
the message and taken to the existing record. If I enter AB into a
new record, I can leave the Initials field without the code firing -
I cannot create a new record and get the Access message. Of the 17
records already present, 7 of the initials cause the code to
execute, but the other 10 do not.

Is any one able to see what I am missing?
Thank you

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) > 0
Then 'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the
record." _ & vbCr & vbCr & "Please check to see that
you are not
entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

.

 

[AccessVandal via AccessMonster.com]

Maybe you'll also need to Cancel the control's event. I don't see you do this
in your code.

Just put it after Me.Undo.
Cancel = True

Access 2003

snip...

 

[AccessVandal via AccessMonster.com]

After reviewing your DCount, I find that you don't need the operator at all.
Just leave DCount without the ">0".

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) Then
'Undo duplicate entry
Me.Undo

Since DCount will always evaluate True in your case. If it is False, it will
skip.

 

[Tim]

Sorry about the slow reply, "Notify" is not sending me e-mails.

The milk doesn't help, I just ended up with foot prints everywhere

I'm not sure how to make the FindFirst run at the start of an If statement
or how to restructure the code to what you suggest (still trying to get a
better foot hold with code).

Thanks Mike!

Give the brownies more milk.

You might try getting rid of the dcount and see if rsc.FindFirst
stLinkCriteria
finds anything.
If it does you can put up your message there.

Thanks Mike,

I agree about Initials used as a key, but it is the database I was
given to work on. I'll see if I can alter how it is set up.

However, I would still like to figure out why the code is not working.

For the AB initial, I retyped it into the db making sure there were no
spaces, and it still passes through the code when trying to create a
new record that also has AB.

For the code, I changed:
SID = Me.Initials.Value
TO
SID = Trim(me.Initials)
and

stLinkCriteria = "[Initials]=" & "'" & SID & "'"
TO
stLinkCriteria = Trim("[Initials]=") & "'" & SID & "'"
- not sure I got the trim correct here

With the changed code, some existing initials pass through the code
and some don't. Any other thoughts?

thanks,
tim

Try trimming the string and checking to see if there are trailing or
leading spaces in the existing strings.
It is possible to introduce leading spaces.
I never use the .value property and Me.Initials will work fine.

That said, using initials as a key, especially if they are names, is
never a good idea. (I once worked in a store with two David Leroy
Smiths.)
Since you will have to use a combo or list box to lookup a particular
record, an index on the lask name will allow picking between Mike
Painter and Mitch Palmer with ease.

"The Text property returns the formatted string. The Text property
may be different than the Value property for a text box control. The
Text property is the current contents of the control. The Value
property is the saved value of the text box control. The Text
property is always current while the control has the focus."
http://msdn.microsoft.com/en-us/library/aa207379(office.10).aspx

Tim wrote:
Access 2003
I have a Contacts form the with the Contacts table as the record
source.
The field Initials is entered by the user and is the primary key for
the table.
The Before Update for this field has code to check for duplicates,
fire a message box if a duplicate is found, and go to the record
with those initials.

The problem I'm having is that the code works sometimes but not
always (when it does not work, the generic Access message about not
allowing duplicate primary key is shown before thee record is saved-
not helpful to the user) - see below for code.

As an example, records with the Initials AK and AB exist. If I try
to enter a new record with the initials AK, the code fires and I get
the message and taken to the existing record. If I enter AB into a
new record, I can leave the Initials field without the code firing -
I cannot create a new record and get the Access message. Of the 17
records already present, 7 of the initials cause the code to
execute, but the other 10 do not.

Is any one able to see what I am missing?
Thank you

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

'Check Contacts table for duplicate Initials
If DCount("Initials", "tbl_Survey_Contacts", stLinkCriteria) > 0
Then 'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the
record." _ & vbCr & vbCr & "Please check to see that
you are not
entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub


.

.

 

[Tim]

Thanks for the tip. However removing the operator still allows the initials
AB to pass but will catch the initials AK (both are already present in the
database).

I do not understand why the code catches some but not others.

Any other thoughts are welcome

 

[AccessVandal via AccessMonster.com]

You might want to create a input mask in that control with just two
charactors limit. Just in case there might be typos inserted into the
field/column of the table.

If it catching the initials for AK, I guess it's working normally. You might
want to check by create a query in Access with a criteria with "AB" and see
if it does the same thing. If does, you might have incorrect data input.

 

[Mike Painter]

Sorry about the slow reply, "Notify" is not sending me e-mails.

The milk doesn't help, I just ended up with foot prints everywhere

I'm not sure how to make the FindFirst run at the start of an If
statement or how to restructure the code to what you suggest (still
trying to get a better foot hold with code).

Thanks Mike!

The parts without ">>>>>" are what I added

*Code*
Private Sub Initials_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria
' Check to see if there is a match
If rst.NoMatch then



else

Me.Bookmark = rsc.Bookmark

 

[Tim]

A query with the critera "AB" shows the person with those initials, but AB
can still be entered as a new contact without the code firing.

 

[Tim]

Gave it try with the new code and get Run-time error 2115 - function is
preventing from saving data in field. Debug is highlighting the Me.Bookmark
line at the end of the If Statement. - New code below -

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria

'Check Contacts table for duplicate Initials
If rsc.NoMatch Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not entering
a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique set of
Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial
Else
'rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

 

[Mike Painter]

A query with the critera "AB" shows the person with those initials,
but AB can still be entered as a new contact without the code firing.

Where did the "AB" or whatever come from to start?
If it was imported, got to the table and manually change "AB" to "AB"
Most unlikely but some punctuation has this problem, most notably when Word
changes "..." to an ASCII elipse.

 

[Mike Painter]

I think I got the code backwards (among other errors.
..NoMatch is true if nothing is found, so

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria

'Check Contacts table for duplicate Initials

If there is a match NoMatch is False so we want to pop the message and then
move to the record.
If not we just skip over the rest.

If rsc.NoMatch = False Then
' or Not rsc.NoMatch

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial

' and the Else should not be here.

 

[AccessVandal via AccessMonster.com]

As Mike points out, check those.

As we don't know what is behind the Form and we can't see your database.
1. Is the form's recordsource query filter?
2. Is the form's FilterOn and the Filter properties set to filter the records?
(check the properties)
3. Is the table field/column index (Initials) and set to No Duplicates?

To use the form's recordsetclone, make sure that the form is not filtered
else it will not work. If it's filtered FindFirst and BookMark will not work.

If you want to use the recordsetclone, you can skip the DCount as Mike had
suggested. But you need to remove any filters in your Form if there's any.

 

[Tim]

Thanks Mike!

It is working now. I was wondering about the If statement. I need to trust
my insticts a bit more.

Cheers ~ Tim

I think I got the code backwards (among other errors.
..NoMatch is true if nothing is found, so

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria

'Check Contacts table for duplicate Initials

If there is a match NoMatch is False so we want to pop the message and then
move to the record.
If not we just skip over the rest.

If rsc.NoMatch = False Then
' or Not rsc.NoMatch

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial

' and the Else should not be here.

'rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

.

 

[Tim]

No filters, data should be entered through the form. Initials is set to no
duplicate.

Mike got his code working, so it now checks for the duplicates. Now, to get
the structure changed so Initials is not the table key.

Thanks for your tips. I consider myself good with Access but no expert with
all the ins and outs.

Thanks again!

 

[AccessVandal via AccessMonster.com]

That's good to hear. Thanks for the feedback.

No filters, data should be entered through the form. Initials is set to no
duplicate.

Mike got his code working, so it now checks for the duplicates. Now, to get
the structure changed so Initials is not the table key.

Thanks for your tips. I consider myself good with Access but no expert with
all the ins and outs.

Thanks again!

 

[FedBrad]

Hi Mike,

Found this earlier thread, and it is similar to what I am trying to do
(prevent entry of a duplicate value into a new record as it is being created,
then redirect to the existing record), but just a little different. Am using
Access 2007 and have two questions...

1) Shold this code work in Ac2007? (code below is from your other thread,
realize I would have to substitute my own specific references to
fields/forms).

2) Can it be placed into the 'On Lost Focus' event, so that I catch the
duplicate and stop data entry right then, rather than the form's Before
Update event? (it is the first data entry field on the form...).

I have been away from building applications in Access for a couple of years,
and am new to 2007. Thanks. (and, if you prefer I try to duplicate it
first, before just asking up front... please say so, I can take it

Brad

I think I got the code backwards (among other errors.
..NoMatch is true if nothing is found, so

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria

'Check Contacts table for duplicate Initials

If there is a match NoMatch is False so we want to pop the message and then
move to the record.
If not we just skip over the rest.

If rsc.NoMatch = False Then
' or Not rsc.NoMatch

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial

' and the Else should not be here.

'rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

.

 

[Marshall Barton]

Found this earlier thread, and it is similar to what I am trying to do
(prevent entry of a duplicate value into a new record as it is being created,
then redirect to the existing record), but just a little different. Am using
Access 2007 and have two questions...

1) Shold this code work in Ac2007? (code below is from your other thread,
realize I would have to substitute my own specific references to
fields/forms).

2) Can it be placed into the 'On Lost Focus' event, so that I catch the
duplicate and stop data entry right then, rather than the form's Before
Update event? (it is the first data entry field on the form...).

I have been away from building applications in Access for a couple of years,
and am new to 2007. Thanks. (and, if you prefer I try to duplicate it
first, before just asking up front... please say so, I can take it

Brad

I think I got the code backwards (among other errors.
..NoMatch is true if nothing is found, so

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

Set rsc = Me.RecordsetClone

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

rsc.FindFirst stLinkCriteria

'Check Contacts table for duplicate Initials

If there is a match NoMatch is False so we want to pop the message and then
move to the record.
If not we just skip over the rest.

If rsc.NoMatch = False Then
' or Not rsc.NoMatch

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning these Initials " _
& "'" & SID & "'" & " have already been entered." _
& vbCr & vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Please check to see that you are not
entering a duplicate Contact." _
& vbCr & vbCr & "Return if neccessary to enter a unique
set of Initials.", _
vbInformation, "Duplicate Information"
'Go to record of original Initial

' and the Else should not be here.

'rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

As long as you have a reference to the DAO library, that
code should work.

If you are filtering the form's data records (via any of
several methods), the duplicate record might not be in the
form's recordset. Note that is usually a good idea to
filter the form's records to the minimum number that are
required to do the job, ideally just one record.