Duplicates Check

Posted on 2012-08-29
Last Modified: 2012-09-03
What is the best way to check for duplicates in a form based on a select query

I have a continuous form where users enter information (a list of numbers). When the list is so long that it goes off the page it would be nice if rather than check to see if they already have the next entry in the list that the program gives message "You already have that number listed." I cannot use the tables no duplicates as the table holds data for a number of lists and will need to store duplicates.
Question by:DatabaseDek
    LVL 61

    Expert Comment

    You could use a check like this in the After Update event of the textbox where the user is entering data:

    Dim rs as dao.recordset
    set rs = me.recordsetclone
    rs.FindFirst "YourField = " & me.txtNumber
    if rs.Nomatch = False then msgbox "This is a duplicate"
    set rs = Nothing

    Open in new window

    The above assumes the field we are checking is numeric.  If it is text, rewrite line 4 like this:

    rs.FindFirst "YourField = '" & me.txtNumber & "'"

    Open in new window

    LVL 13

    Assisted Solution

    A possibly cool alternative to yours, but I used this trick last week in .NET but it'd work in Access.

    You have your table of displayed data. Then you have a set of textboxes for each column. The textboxes set the form filter, using wildcards at the end. So as you fill in the boxes, the data is filtered to what you typed.  

    If what you've type isn't there, the list will become empty (so you know it's new).  Then you have a button to click which adds the record using the info in the text boxes.

    Just a fancy idea.

    Assisted Solution

    Hi mbizup

    I get error on Me,TextNumber data or method not found line 4 when changed to text mode

    MrBullwinkle Very nice and useful tip for alternative.
    LVL 61

    Accepted Solution

    You need to replace that with the actual name of the textbox that contains the number the user is entering.

    This line is just for example purposes (I don't know the names of the controls on your form or the fields in your table).

    >>>> rs.FindFirst "YourField = '" & me.txtNumber & "'"

    You need to edit it like this:

    rs.FindFirst "WhateverTheNameOfYourFieldIs = '" & me.WhateverTheNameOfYourTextboxIs & "'"

    Author Closing Comment

    Thank you both

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    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…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now