How to avoid duplicates in append query (1 Viewer)

ivonsurf123

Registered User.
Local time
Today, 02:11
Joined
Dec 8, 2017
Messages
69
Hello,

I am trying to check on duplicates if I enter the data again, but it only works if I enter Both values but if I enter just one it take it as new entry and not recognized that single value already exist in Table, Example: if I have the Vendor and Code it will tell me that value already exists in tbl_CDS_DRA_Key_Master_Codes, but if I do it again but this time I add Vendor only it will record the data, How can I prevent that and recognize whether the Vendor or Code already exists in tbl_CDS_DRA_Key_Master_Codes, data in coming from another table named: tbl_CDS_DRA_Key_Legend


If DCount("*", "tbl_CDS_DRA_Key_Master_Codes", "[Vendor]='" & Me.txtExtractType & "' AND
Code:
='" & Me.txtCode & "'") > 0 Then

                  MsgBox "This code already exists in the Master Code File!" & vbCrLf & _
                      "Vendor: " & Me.txtExtractType & vbCrLf & _
                      "Code: " & Me.txtCode & vbCrLf, vbCritical, "Already Exists"
        Me.Undo
        Cancel = True
        
       Else
                                                                               
          strNewDRA = "INSERT INTO tbl_CDS_DRA_Key_Master_Codes " & _
                        "([Vendor],[Code]) VALUES ('" & Me.txtExtractType & "','" & Me.txtCode & "'); "
                        CurrentDb.Execute strNewDRA
                        MsgBox "DRA Key Moved to the Master Codes File Completed", vbInformation, "DRA Key"
                    
      End If   
:banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:11
Joined
May 7, 2009
Messages
19,170
Use OR and not AND on yout Dcount()
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:11
Joined
Jan 20, 2009
Messages
12,849
Do take heed of jdraw's post too.

A composite index should be present to ensure that duplicates cannot be entered under any circumstance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,981
Whenever RI (Referential Integrity) can do something for you, you should use RI instead of relying on your own code. If each column must be unique by itself, you need two separate unique indexes. When the combination of the two columns must be unique, you need ONE compound index that includes both columns.

Having the indexes prevents queries and other processes from adding invalid data. Your code only works for the form where it is running and possibly not in all cases unless you have put the code into the correct event - which is the form's BeforeUpdate event. If your code is in any other event, it will not actually protect anything.

PS - undoing all the changes to the form is punitive. Why not just undo the value for the field that is duplicated? Of course you would have to separate the DLookup() into two parts so you can identify which field has the error. The suggested code using the OR can't distinguish and so if you want to undo, you have no alternative but to undo BoTH or even the entire form.
 

Users who are viewing this thread

Top Bottom