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.

 

[Pat Hartman]

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.