VB code for addressing duplicates in Access tables

Posted on 2012-08-28
Medium Priority
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
  • 5
  • 4
LVL 74

Expert Comment

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

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

Author Comment

ID: 38342448
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

ID: 38342492
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?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 38342678
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

Jeffrey Coachman earned 1500 total points
ID: 38342957

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

ID: 38343195
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
ID: 38348982
<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.


Author Comment

ID: 38352227
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
ID: 38385482
Again, your original approach was not the standard way this is typically done.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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