VB code for addressing duplicates in Access tables

Posted on 2012-08-28
Last Modified: 2012-09-10
I previously got assistance with code so that if data within a field already exists within the table, then it was flag stating a duplicate existed.  It worked perfectly until a new table was uploaded and then it flagged everything field as a duplicate even though it wasn't.

I then received some updated code which fixed this issue; however, it created another problem.  If i copy the data in one record and apply it to a second record, it will allow it to save even though it is a duplicate.  But afterwards if i try changing any other data in that record, it then flags it as a duplicate.  What changes can i make to the below code where it recognizes a duplicate regardless of the above circumstances.    

All data is managed through a form.  

Below is the second piece of code.  The first only had > 0 at the end of the first line.  
Private Sub Form_BeforeUpdate(Cancel As Integer)

 'Searches XT2_Inventory table to ensure air sim number is not already listed.  If so, then it is flagged.
If DCount("Air_SIM", "XT2_Inventory", "Air_SIM=" & Chr(34) & Me.Air_SIM & Chr(34)) > (1 + Me.NewRecord) Then
   MsgBox "Duplicate Air SIM on Regional Inventory", vbExclamation, "Duplicate Error"
   Cancel = True
End If

Let me know if you have any questions.  I can attach a sample data, but will have to strip it of all the info which is why i didn't post initially.
Question by:jsawicki
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    I know you have already heard this but...

    What the reason this can't be based on a autonumber field...?

    Author Comment

    Is this the feature that doesn't allow there to be duplicates in the database meaning I go into the table and select the indexed field that says yes no duplicates?  it can't be an autonumber field since these numbers provided to us by a vendor.  

    I think i did hear this, but am i on the right track for this function since if i can do it within the form/table without additional coding, that would be great.  


    Author Comment

    And this is through the indexes function within the table.  Can that data type be text and when that feature is opened, all i need to do is enter the info manually?

    Author Comment

    Boag:  The other reason for creating code is so i can create a unique message for the duplicate since there are several fields that will be using this code/validation.  Setting the field as an index just provides a generic message so one would not know where the duplicate is if there have changed multiple fields.
    LVL 74

    Accepted Solution


    I just wanted to be sure....
    I know that sometimes Autonumber is not an option.

    But as you can see from your existing code, detecting a duplicate is easy...

    However you use of:
     (1 + Me.NewRecord) is a bit confusing, and may be part of the problem.
    If you are in a new record, then Me.NewRecord will return value of -1 (NewRecord=True)
    Any other time it will produce a Zero

    So I am confused why you are using this...?  Typically to see if there is a dupe, you simply see if a count is greater then Zero.

    Add to this the typical syntax to take into account new records, and you end up with something like this on the current event of the form:
    If me.NewRecord then
        If Dcount (.......)>0 then
            msgbox "This is a dupe"
            'Do something else possibly
        end if
    end if

    Finally the "Event" you put this on is also a concern.
    (Typically validation code like this is on the Before or After update event of a "control")
    I see you have a Cancel=True in your code, this typically indicates a Before_XXXX event.
    Because Before_xxxx events have a Cancel argument.

    It is also not clear if the form is "dirty" or not when this code is being run.
    It is not clear when or how the "next" number is generated.
    This is why it is always clearer if you post a sample database and clearly state how to replicate the issue and also include a clear indication of exactly what you need.


    Author Comment

    So i posted a sample  database.  If you take any one of the SIM numbers and post in another record, you will see it will save when save is selected.  Then if you manipulate any other field after the save, you will get the error message stating it is a duplice.  There are 16 records, most are blank and i was just adding them to a blank record.  The save code does recognize me.dirty.  

    Basically, my desired output is anytime there is a duplicate in the form field for those selected records, it flags it.  Like i said, it was working when the code was written with the existing data, but when i uploaded a new table for some reason it changed the way it viewed that record initially.  The code is written for all the fields call SIM.  

    The other code i have for ensuring the number exists in the separate table which uses =0 works without a hitch even if new data is imported.  

    Let me know what you discover.  Thanks.....
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <If you take any one of the SIM numbers,>
    SIM Number?
    What is a SIM Number?
    Where is this field? a table?
    Which table?

    Sample database notes:
    1. Back up your database(s).
    2. Combine the front and back ends into one database file.
    3. Remove any startup options, unless they are relevant to the issue.
    4. Remove any records unless they are relevant to the issue.
    5. Delete any objects that do not relate directly to the issue.
    6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
    7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
    8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
    9. Unhide and hidden database objects
    10. Compile the code. (From the VBA code window, click: Debug-->Compile)
    11. Run the compact/Repair utility.
    12. Remove any Passwords and/or security.
    13. If a form is involved in the issue, set the Modal and Popup properties to: No
        (Again, unless these properties are associated with the issue)
    14. Post the explicit steps to replicate the issue.
    15. Test the database before posting.

    In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
    And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


    Author Comment

    Sorry Coachman, i thought the instructions were clear since the form fields were named SIM in 3 different areas and the code would explain how it was supposed to function.  SIM is a number assigned to the wireless equipment and to us it is irrelevant other than having it accurately documented which is the problem and why the code was created.  

    If you take the field titled SIM # under Wireless Equipment and go to another existing record and paste in the same field, initiate save using the command button, it will allow you to save whereas it shouldn't since it already exists.  Then if you make any change on that same record and hit save again, you will then receive a message stating it is a duplicate.  What i don't understand is why it isn't recognizing a duplicate when i first copy it and only after the record is manipulated the first time does it recognize it as a duplicate.  

    Let me know if this is still not clear.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Again, your original approach was not the standard way this is typically done.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    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…
    Familiarize people with the process of utilizing SQL Server functions 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 Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    737 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

    17 Experts available now in Live!

    Get 1:1 Help Now