How to avoid duplicates in append query

[ivonsurf123]

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

='" & 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:

 

[jdraw]

You might research this link re composite unique index to prevent duplication of data.

 

[arnelgp]

Use OR and not AND on yout Dcount()

 

[ivonsurf123]

Thank you arnelgp! I was complicating myself

 

[Galaxiom]

Do take heed of jdraw's post too.

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