Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Checking for duplicates before saving record

Posted on 2011-10-06
15
Medium Priority
?
355 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Chrisjack001
  • 8
  • 4
13 Comments
 

Author Comment

by:Chrisjack001
ID: 36924579
I split them into 2 because the question may be too long for 1 question
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36924766
are you looking for help with the queries or with the forms?
0
 

Author Comment

by:Chrisjack001
ID: 36924816
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Chrisjack001
ID: 36924913
I'm sure there is some vba to it too
0
 

Author Comment

by:Chrisjack001
ID: 36925460
Thanks. I deleted the other question. I will ask that after this is solved
0
 
LVL 40

Expert Comment

by:als315
ID: 36926295
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
 

Author Comment

by:Chrisjack001
ID: 36926724
Can this query be incorporated in the patient form property. If so where can I insert it. In what event
0
 
LVL 40

Expert Comment

by:als315
ID: 36928808
It is used in last check in BeforeUpdate event on this form
0
 

Author Comment

by:Chrisjack001
ID: 36931290
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
 
LVL 40

Expert Comment

by:als315
ID: 36932525
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
 

Author Comment

by:Chrisjack001
ID: 36932594
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
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 36932895
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
 

Author Closing Comment

by:Chrisjack001
ID: 36933182
Thanks a lot for your help
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question