Checking for duplicate names in record creation

Posted on 2011-10-03
Last Modified: 2012-05-12
I have a form called “Patient” which is used to add new patients to the database.  I have a vba code in my “Before Update” event of the form that checks the “Last Name” and “DOB” for duplicates before adding the new patient. Due to historic data that has been loaded to the patient table, there are patients that don’t have date of birth (DOB) and as a result of that I have to change the code used for checking  duplicates. I want the VBA to only check “Last Name” if the “First Name” & “DOB” are null but can check the other fields “UTSW MRN” or “Parkland MRN” if not Null. For example if a patient “Last Name” is entered without a “first name” and “DOB” but a “UTSW MRN” is entered that matches that last name and MRN with an existing patient, a message box should display saying there is a similar record in that database.  How can I accomplish this goal. Attached is a copy of my database with the form. Thanks in advance.
Question by:Chrisjack001
    LVL 13

    Accepted Solution

    Look at the attached database so you can get an idea.   But basically i created 3 functions that would check for your duplicates which you can change.  Remember this is just an idea.

    Each time the value changes on the patient form, the before update event on the control will check for a duplicate.

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    As lucas911 will tell you, systems like this can start to slow down the DB, if you start having to check more fields and as the number of records grows.

    Consider frontline techniques like making certain fields required.
    My business is mostly Medical.
    In a medical DB, there should not be an allowable reason for a patient's DOB to be blank.
    For example, even in an emergency situation, the DOB is "approximated", butt never left blank.

    The issue with using "Other fields" is that it just complicates things unnecessarily...

    Just my 2c

    Author Comment

    You are absolutely correct boag2000. The only problem with these records are the patients are not required to provide all this information because they are used for research purposes. Example all the historic data we have to be uploaded are missing DOBs, Some first names etc.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Just an fyi...

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Read about achieving the basic levels of HRIS security in the workplace.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now