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

.

 

[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

 

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

 

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

 

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

 

[FedBrad]

Marshall,

Thanks for the quick reply... below is my code. DAO lib reference is
present, and no filters are applied to the underlying table. In fact, since
I am building from scratch, the underlying table currently only has one
existing record - am entering duplicate HICN into the second record to test
this validation scheme.

BID is mine (instead of SID), the field being evaluated is [HICN], and I
used the older fashioned way to do the message box. Oddly enough, the code
seems to be hanging on the very last line:

'**************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim BID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Msg. Style, Title, Reponse
Set rsc = Me.RecordsetClone
BID = Me.HICN.Value
stLinkCriteria = "[HICN]=" & "'" & BID & "'"
rsc.FindFirst stLinkCriteria
'Check for duplicate HICN
If rsc.NoMatch = False Then

'Undo duplicate entry
Me.Undo
'Message box warning of duplication
Msg = " My msg....."
Style = vbOKOnly + vbInformation
Title = "Duplicate HICN"
Response = Msgbox (Msg, Style, Title)
'Go to original record with the duplicate HICN
Me.Bookmark = rsc.Bookmark << hanging here...

End If
Set rsc = Nothing

End Sub
'**********************************************
Duplicate HICN is detected, new record being created gets undone, message
fires fine, but does not redirect to the record containing the duplicated
HICN (i.e., in the same form) - at this point, the only other record in the
table. Anything you can see that is out-to-lunch?

Thanks

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.

 

[Marshall Barton]

Thanks for the quick reply... below is my code. DAO lib reference is
present, and no filters are applied to the underlying table. In fact, since
I am building from scratch, the underlying table currently only has one
existing record - am entering duplicate HICN into the second record to test
this validation scheme.

BID is mine (instead of SID), the field being evaluated is [HICN], and I
used the older fashioned way to do the message box. Oddly enough, the code
seems to be hanging on the very last line:

'**************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim BID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Msg. Style, Title, Reponse
Set rsc = Me.RecordsetClone
BID = Me.HICN.Value
stLinkCriteria = "[HICN]=" & "'" & BID & "'"
rsc.FindFirst stLinkCriteria
'Check for duplicate HICN
If rsc.NoMatch = False Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
Msg = " My msg....."
Style = vbOKOnly + vbInformation
Title = "Duplicate HICN"
Response = Msgbox (Msg, Style, Title)
'Go to original record with the duplicate HICN
Me.Bookmark = rsc.Bookmark << hanging here...
End If
Set rsc = Nothing

End Sub
'**********************************************
Duplicate HICN is detected, new record being created gets undone, message
fires fine, but does not redirect to the record containing the duplicated
HICN (i.e., in the same form) - at this point, the only other record in the
table. Anything you can see that is out-to-lunch?

That code looks ok to me.

What actually happens? Did you get an error message?

Your idea of the meaning of the word "hang" may not be the
same as what it means to me (Access stops responding), so a
clarification is needed.

The only guesses I can come up with at this point is that
you did not click the message box's OK button, there is some
other code getting in the way, or your db is corrupted.

 

[FedBrad]

Thanks again... sorry about misuse of term (hanging). Anyway, no error
message, etc. The current record (duplicate HICN attempt) simply clears
(Me.Undo), msg fies, I do click the 'OK' button, and focus just remains on
the empty new record.

Is there a method to point to the desired existing record (GoToRecord) using
the rsc.FindFirst stLinkCriteria somehow, rather than relying on
Me.Bookmark=rsc.Bookmark?

Stumped...

Thanks for the quick reply... below is my code. DAO lib reference is
present, and no filters are applied to the underlying table. In fact, since
I am building from scratch, the underlying table currently only has one
existing record - am entering duplicate HICN into the second record to test
this validation scheme.

BID is mine (instead of SID), the field being evaluated is [HICN], and I
used the older fashioned way to do the message box. Oddly enough, the code
seems to be hanging on the very last line:

'**************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim BID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Msg. Style, Title, Reponse

Set rsc = Me.RecordsetClone

BID = Me.HICN.Value
stLinkCriteria = "[HICN]=" & "'" & BID & "'"

rsc.FindFirst stLinkCriteria

'Check for duplicate HICN
If rsc.NoMatch = False Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
Msg = " My msg....."
Style = vbOKOnly + vbInformation
Title = "Duplicate HICN"
Response = Msgbox (Msg, Style, Title)
'Go to original record with the duplicate HICN
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'**********************************************
Duplicate HICN is detected, new record being created gets undone, message
fires fine, but does not redirect to the record containing the duplicated
HICN (i.e., in the same form) - at this point, the only other record in the
table. Anything you can see that is out-to-lunch?

That code looks ok to me.

What actually happens? Did you get an error message?

Your idea of the meaning of the word "hang" may not be the
same as what it means to me (Access stops responding), so a
clarification is needed.

The only guesses I can come up with at this point is that
you did not click the message box's OK button, there is some
other code getting in the way, or your db is corrupted.

 

[FedBrad]

Hi Marshall,

The value of playing around a little... I just opened the form concerned,
still with only the one record in it. Then attempted to close the record,
and the code fired - unexpected... a couple of things struck me.

First, it appears it is finding itself (current record) and considering it a
duplicate. So, there must be something wrong with the way I am attempting to
find a 'different' existing record with the same HICN value?

Thanks again... sorry about misuse of term (hanging). Anyway, no error
message, etc. The current record (duplicate HICN attempt) simply clears
(Me.Undo), msg fies, I do click the 'OK' button, and focus just remains on
the empty new record.

Is there a method to point to the desired existing record (GoToRecord) using
the rsc.FindFirst stLinkCriteria somehow, rather than relying on
Me.Bookmark=rsc.Bookmark?

Stumped...

Thanks for the quick reply... below is my code. DAO lib reference is
present, and no filters are applied to the underlying table. In fact, since
I am building from scratch, the underlying table currently only has one
existing record - am entering duplicate HICN into the second record to test
this validation scheme.

BID is mine (instead of SID), the field being evaluated is [HICN], and I
used the older fashioned way to do the message box. Oddly enough, the code
seems to be hanging on the very last line:

'**************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim BID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim Msg. Style, Title, Reponse

Set rsc = Me.RecordsetClone

BID = Me.HICN.Value
stLinkCriteria = "[HICN]=" & "'" & BID & "'"

rsc.FindFirst stLinkCriteria

'Check for duplicate HICN
If rsc.NoMatch = False Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
Msg = " My msg....."
Style = vbOKOnly + vbInformation
Title = "Duplicate HICN"
Response = Msgbox (Msg, Style, Title)
'Go to original record with the duplicate HICN
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'**********************************************
Duplicate HICN is detected, new record being created gets undone, message
fires fine, but does not redirect to the record containing the duplicated
HICN (i.e., in the same form) - at this point, the only other record in the
table. Anything you can see that is out-to-lunch?

That code looks ok to me.

What actually happens? Did you get an error message?

Your idea of the meaning of the word "hang" may not be the
same as what it means to me (Access stops responding), so a
clarification is needed.

The only guesses I can come up with at this point is that
you did not click the message box's OK button, there is some
other code getting in the way, or your db is corrupted.

 

[Marshall Barton]

The value of playing around a little... I just opened the form concerned,
still with only the one record in it. Then attempted to close the record,
and the code fired - unexpected... a couple of things struck me.

First, it appears it is finding itself (current record) and considering it a
duplicate. So, there must be something wrong with the way I am attempting to
find a 'different' existing record with the same HICN value?

Interesting! If the form's BeforeUpdate event is troggered
before you even navigate to the new record or edot the
existing record, then you have some code somewhere that is
making the record dirty. This is a bad thing to do and you
need to find and remove that code.

OTOH, it also raises the issue of running the code when you
edit an existing record. This can be avoided by either
moving the code to the text box's BeforeUpdate event or by
adding more code to check if the current record is a new
record:

If Me.NewRecord Then
'your code here
End If

Either way, it would also be a good idea to first check if
the field has been edited:

If Me.txtbox = Me.OldValue Then Exit Sub

I would not think that FindFirst would normally find itself
for a new record, but you could guard against that situation
by checking the table rather than the form's recordset.

If IsNull(DLookup("1", "thetable", "Initials='" & SID &
"'")) Then Exit Sub