VB code for addressing duplicates in Access tables
Posted on 2012-08-28
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
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.