Problem with code to catch duplicate Primay Key

[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

 

[FedBrad]

Yep... So, I did move it to the LostFocus event (for the HICN field), but
still had similar results. I'll fiddle as you have suggested below, and let
you know whre it takes me.

Thanks again... fb

 

[FedBrad]

Marsh,

"You Da Man!" I added the lines you suggested (w/o the DLookup statement),
moved it to BeforeUpdate, and this is now responding exactly as I intended.
However, I seem to have created another challenge in the process.

If I simply go to a new record and enter nothing, then attempt to leave that
record (go back to a different record or close), I get a debug error "Invalid
use of Null" on the following line:

BID = Me.HICN.Value

Mousing over indicates the value is, in fact, being evaluated as Null.
Being a textbox, I would think it would simply see as a zero length string
("") - but not sure if that makes a difference?

If I can't resolve it, are you okay with me including the full event code
for you to look over - may have been lost along the way in this thread...?

 

[Marshall Barton]

"You Da Man!" I added the lines you suggested (w/o the DLookup statement),
moved it to BeforeUpdate, and this is now responding exactly as I intended.
However, I seem to have created another challenge in the process.

If I simply go to a new record and enter nothing, then attempt to leave that
record (go back to a different record or close), I get a debug error "Invalid
use of Null" on the following line:


Mousing over indicates the value is, in fact, being evaluated as Null.
Being a textbox, I would think it would simply see as a zero length string
("") - but not sure if that makes a difference?

If I can't resolve it, are you okay with me including the full event code
for you to look over - may have been lost along the way in this thread...?

A BeforeUpdate event (ehich one? A text box or the form?)
will not fire unless something in the text box or the record
has been changed. So, if you did not do anything on the new
record, then you must have some code somewhere that is
setting a bound contriol (or field). If you do have some
code that does that, it should be removed/revised so it dees
not make the record dirty.

You can post a Copy/Paste of your code, but if there is a
lot of it. I might not be able spot some nuance that is
messing you up.