jsawicki
asked on
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.
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.
ASKER
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.
Thanks
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.
Thanks
ASKER
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.....
XT2-Inventory.accdb
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.....
XT2-Inventory.accdb
<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?
...ect
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.
JeffCoachman
SIM Number?
What is a SIM Number?
Where is this field?
...in a table?
Which table?
...ect
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.
JeffCoachman
ASKER
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.
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.
Again, your original approach was not the standard way this is typically done.
What the reason this can't be based on a autonumber field...?