Open record if duplicate found

[coachjeffery]

I have a form EP Data which has a bound text box PatientID (number, indexed).
All of the data is stored in the Patients table. When the user keys in the
PatientID number i want the BeforeUpdate event to search the Patient table
for an existing record, if found, ask if they want to edit the record or key
in a new PatientID number. If they want to edit the existing record, i want
it to open that record. The form also has a sub form EP Data subform.

Here is the code i have so far based on what i have gathered from other
posts to this site.

Private Sub PatientID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("PatientID", "Patients", "PatientID=" &
PatientID.Value)) Then
Cancel = (MsgBox("A record exists for this PatientID, do you want to
edit the record?", vbQuestion + vbYesNo) = vbNo)
End If
End Sub

If I click No, it sets the focus back to the PatientID field which is what i
want. If I ckick yes, It does open the related subform but I get an error
"The changes to the table were not successful because it would create
duplicate values..."

Any thoughts on what i am missing? Thanks in advance for the assistance.

 

[Allen Browne]

Before you can move to the other record, you will need to undo the one you
are creating/editing:
Me.Undo

If you still have problems, try the PatientID_AfterUpdate event, as this
will be more flexible with what you can do.

You may need to also adjust your code so that it does not find an existing
record as a duplicate if you change the PatientID back to its OldValue.

 

[CoachJeffery]

If I include the Me.Undo code, it will wipe out the PatientID currently
entered which was found to be a duplicate. What i want is "if record found,
then open.

So far my code recognizes that the PatientID already exists in the table,
it's just not opening the record.

 

[Allen Browne]

If the record is found, you are going to have to perform a FindFirst based
on the primary key you found, not the primary key of the new record that was
being entered and is about to be discarded.

 

[CoachJeffery]

How do i go about performing the FindFirst you are recommending?

 

[Allen Browne]

There is an example of using a FindFirst in this article:
http://allenbrowne.com/ser-03.html

While the article refers to an unbound combo, once you have undone your
record, and looked up the primary key value, the FindFirst part will be
similar.