Insert into with values but ignoring duplicates

[breezett93]

I've got a procedure that is first splitting up a field and creating new records; then immediately dumping those records into an assigned table.
Currently, if I enter the same record number over and over, duplicate records are just added into the table instead of overwriting changes to the fields.

Here's an example of what's happening:

Source Table
Parent | Child
-------------
1234 | 13, 27, 189, 210

This is the code that does the formatting:

Set d = CurrentDb
    Set R = d.OpenRecordset("SELECT* From OrdTbl WHERE [OrdId] = " & [Forms]![frmSampleChildOrd]![txtSampleNumber])
    With R
        If Not .BOF And Not .EOF Then
            While Not .EOF

                vParent = .Fields("OrdId") & ""
                vChild = .Fields("ChildOrd")
                ARR = Split(vChild, ",")

which makes the data look like this:
Parent | Child
-------------
1234 | 13
1234 | 27
1234 | 189
1234 | 210

Which then gets dumped into Destination Table.

For i = LBound(ARR) To UBound(ARR)
                    sSQL = "INSERT INTO tblSampleChildOrd (ParentOrd,ChildOrd) VALUES ('" & vParent & "','" & ARR(i) & "')"
                    'Debug.Print sSQL
                    CurrentDb.Execute sSQL, dbFailOnError
                Next

                Erase ARR

                .MoveNext
            Wend
        End If

So, if I enter 1234 again, I get this:
Parent | Child
-------------
1234 | 13
1234 | 27
1234 | 189
1234 | 210
1234 | 13
1234 | 27
1234 | 189
1234 | 210

I do not want duplicate Child values. How can I adjust my Insert statement to make that happen?

 

[Andrzejek]

You can do:

Set R = d.OpenRecordset("SELECT [blue]DISTINCT[/blue] * From OrdTbl WHERE ...

Or, how about checking if this value is already in the table before Insert

For i = LBound(ARR) To UBound(ARR)[blue]
    If IsNull([url=https://support.microsoft.com/en-us/office/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937]DLookup(yada, yada, yada)[/url]) Then[/blue]
        sSQL = "INSERT INTO tblSampleChildOrd (ParentOrd,ChildOrd) VALUES ('" & vParent & "','" & ARR(i) & "')"
        'Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError[blue]
    End If[/blue]
Next

---- Andy

There is a great need for a sarcasm font.