Checking for duplicates before saving record

I have a form called “Scheduling-Add” which is used to schedule a patient. This form has a “Patient Name” button when clicked will open a “Patients” form used to add a new patient. I want to use the following criteria to check for duplicate records before saving a new patient record

First check both MRN fields (Not required field) to see if they exist in the “Patient” table. If they do, a message should display “Duplicate exist and cannot save to database” if not duplicate check other fields that were input in the entry before saving

Second, check for “Last Name” (Required Field). There must always be a last name. If that field is blank a message should pop up that Last name is required. If a last name is present including other fields, this is checked in the table to see if there is a duplicate. If it does message is displayed “name exist and cannot save” If not duplicate save to table

Third if the new record has No “first name” & No “DOB” are Null (Not required fields), it checks the last name and any other field filled to see if it exist. If it does message is displayed “last name exist and cannot save” or if not duplicate save record.

How can I accomplish this goal. Attached is a copy of my database. Thanks in advance

Invoice-7-7-2001-91611.accdb
Chrisjack001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chrisjack001Author Commented:
I split them into 2 because the question may be too long for 1 question
0
momi_sabagCommented:
are you looking for help with the queries or with the forms?
0
Chrisjack001Author Commented:
The query to check for duplicates based on that criteria before saving the new data. I already have the form. If you check my database I attached its in there.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chrisjack001Author Commented:
I'm sure there is some vba to it too
0
Chrisjack001Author Commented:
Thanks. I deleted the other question. I will ask that after this is solved
0
als315Commented:
Try this sample. PatientCheck query is the base of checking for duplicates (you can also add to this query MRN fields in the same manner)
Invoice-7-7-2001-91611--3-.accdb
0
Chrisjack001Author Commented:
Can this query be incorporated in the patient form property. If so where can I insert it. In what event
0
als315Commented:
It is used in last check in BeforeUpdate event on this form
0
Chrisjack001Author Commented:
Are you saying copy this query from "PatientCheck" and paste in the Before Update. I already have this other VBA code in there which I came up with for my MRN numbers. Can you tell me how I can incorporate both or how best to do this in the before update.

SELECT IIf(IsNull([Forms]![Patients]![FirstName]),True,IIf([Forms]![Patients]![FirstName]=[Patient]![FirstName],True,False)) AS FN, IIf(IsNull([Forms]![Patients]![MiddleName]),True,IIf([Forms]![Patients]![MiddleName]=[Patient]![MiddleName],True,False)) AS MN, IIf(IsNull([Forms]![Patients]![LastName]),True,IIf([Forms]![Patients]![LastName]=[Patient]![LastName],True,False)) AS LN, IIf(IsNull([Forms]![Patients]![DOB]),True,IIf([Forms]![Patients]![DOB]=[Patient]![DOB],True,False)) AS DB
FROM Patient
WHERE (((IIf(IsNull([Forms]![Patients]![FirstName]),True,IIf([Forms]![Patients]![FirstName]=[Patient]![FirstName],True,False)))=True) AND ((IIf(IsNull([Forms]![Patients]![MiddleName]),True,IIf([Forms]![Patients]![MiddleName]=[Patient]![MiddleName],True,False)))=True) AND ((IIf(IsNull([Forms]![Patients]![LastName]),True,IIf([Forms]![Patients]![LastName]=[Patient]![LastName],True,False)))=True) AND ((IIf(IsNull([Forms]![Patients]![DOB]),True,IIf([Forms]![Patients]![DOB]=[Patient]![DOB],True,False)))=True));
Code-BeforeUpdate.JPG
0
als315Commented:
No, all code is already there (in my example):
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Patient", "[UTSW MRN] = '" & Me.Text6 & "'") > 0 Or _
    DCount("*", "Patient", "[Parkland MRN] = '" & Me.Text9 & "'") > 0 Then
        MsgBox "Duplicate exist and cannot save to database", vbOKOnly
        GoTo err
End If
If IsNull(Me.LastName) Then
        MsgBox "Last name is required", vbOKOnly
        GoTo err
End If
    
If DCount("*", "PatientCheck") > 0 Then
 MsgBox "Similar record exist in database!"
 GoTo err
End If
Exit Sub
err:
    Cancel = True
End Sub

Open in new window

0
Chrisjack001Author Commented:
What is I wanted to add these other criterias to it.

If the new record has No “first name” Null but has a “DOB”, it checks the “last name” with the “DOB” and any other field filled to see if it exist. If it does message is displayed “name exist and cannot save” or if not duplicate save record.

If the new record has No “DOB” Null but has a “First Name”, it checks the “last name” with the “First Name” and any other field filled to see if it exist. If it does message is displayed “name exist and cannot save” or if not duplicate save record.

If the new record has a “first name” & a “DOB”  (Not required fields), it checks the “First name”, “DOB” and “last name” and any other field filled to see if it exist. If it does message is displayed “ name exist and cannot save” or if not duplicate save record.

Lastly if nothing is entered, don’t save. Display message saying “Data required or close to Exit”.
0
als315Commented:
Query "PatientCheck" is doing this work. Have you tested my example?
Test also this sample with save and cancel buttons
Invoice-7-7-2001-91611--3-.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chrisjack001Author Commented:
Thanks a lot for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.