VB code for addressing duplicates in Access tables

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.
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:

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.

Jeffrey CoachmanMIS LiasonCommented:
I know you have already heard this but...

What the reason this can't be based on a autonumber field...?
jsawickiAuthor Commented:
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.  

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jsawickiAuthor Commented:
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?
jsawickiAuthor Commented:
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.
jsawickiAuthor Commented:
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.....
Jeffrey CoachmanMIS LiasonCommented:
<If you take any one of the SIM numbers,>
SIM Number?
What is a SIM Number?
Where is this field?
...in 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, ...post 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.

jsawickiAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Again, your original approach was not the standard way this is typically done.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.