Prevent Entering Duplicate Record

Posted on 2011-05-09
Last Modified: 2013-11-27
On a form with fields of FirstName and LastName I would like to prevent user from entering the same person twice based solely upon those two fields. I assume this would occur in the afterupdate event for each control.  Could you please provide the code and possibly a message to alert the user.  Thanks
Question by:zpotok
    LVL 75

    Accepted Solution

    see image ... create a Unique Index on those two fields ... and Access will automatically prevent this from happening ... no code.

    LVL 30

    Assisted Solution

    To check for duplicate record to intruduce your defined messages, try this:
    Table a (f1, f2). If the current record is a duplicate, it displays the message and cancels the saving of the record.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim c As Integer
        c = DCount("f1", "a", "f1='" & Me.f1 & "' and f2='" & Me.f2 & "'")
        If c > 0 Then
            MsgBox ("duplicate record, please modify")
            Cancel = True
        End If
    End Sub
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    This question has been discussed many times.

    What hnasr and MX posted will address your specific request.

    Just some "FYI" Notes here:
    The issue with any "Prevent Duplicates" request is that the definition of "Duplicates" is open to interpretation.

    First Name: Jeffrey
    Last Name: Coachman

    By your definition, only Jeffrey Coachman would constitute a duplicate.
    This means that all of the following would be allowed even though it is the same person:
    Jeffery Coachman
    Jeffrey Coachmen
    Jeff Coachman
    J Coachman
    Geoffrey Coachman
    First Name: Jeffrey (Last Name blank)
    Last Name: Coachman (First name Blank)
    What's your plan for records like this?

    Sure you can add more field to the check, but the same issues with misspellings still apply...
    And also the more fields you try to check, the longer this will take (especially if the list is fairly long)

    Also remember that in the current Business environment many people will want to set up two accounts, one Business and one Personal.
    In this case both sets of names need to be the same, and only the "Type" will be different (Cust Type, Account Type, Member type, ...etc)
    What;'s your plan for this contingency?

    Again, Just FYI




    Author Closing Comment

    Thank you all..
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now