Stop duplicates and GoTo duplicate record

[sneagle]

I have a pop-up form for finding a code to enter on a main form. That
process works fine. However, I want the user to have the ability to enter a
new code into the underlying 'codes' table. The codes are numeric. After
the user enters a new code, if that code exists already, I want to stop them
and take them to that record. Below is the code I am using. The cancel
seems to work, then I get

"Run time error '2108' You must save the field before you execute the
GoToControl action, the GoToControl method or the SetFocus method."

Any suggestions?

Thanks.

Private Sub ICD9_beforeUpdate(Cancel As Integer)

Dim DuplicateICD9

If DCount("ICD9", "ICD9Codes", "[ICD9] = '" & Me.ICD9 & "'") > 0 Then
DuplicateICD9 = Me.ICD9
Cancel = True
MsgBox ("Already in use: " & DuplicateICD9)
Me.Undo

strFiltre = "([ICD9] + ""*"")"
DoCmd.ApplyFilter FilterName:=strFiltre

Me.ICD9.SetFocus
DoCmd.FindRecord DuplicateICD9, acAnywhere
End If

End Sub

 

[Anne]

I don't remember where I got this code, but it is not mine. It is meant to
detect a duplicate social security number when the user enters a new
employee. The social security number or SSN is not the primary field, I am
not sure it that makes a difference.
See if you can make this code work for your case.

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

Set rsc = Me.RecordsetClone

EID = Me.SSNo.Value
stLinkCriteria = "[ssNo]=" & "'" & EID & "'"

'Check EmployeeDetails table for duplicate SocialSecurityNumber
If DCount("SSNo", "tblEmployeeList", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Social Security Number " _
& SSNo & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
'Go to record of original Employee Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

 

[sneagle]

Thanks for the info. In the end, I played with my code and found removing
the line ' Me.ICD9.SetFocus' stopped the error. Now it works. I can't
explain why, but I am glad.