Open record if duplicate found

C

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top