how to not insert duplicates

[ricknicholson]

I have an Insert sql statement but am strugling with how not to have it
insert any duplicates LOTNUM_72 if already exists. Any help is
appreciated.

sSQL = "INSERT INTO [LotTrackingHistory] (LOTNUM_72, TNXDTE_72,
PRTNUM_72, USRNAM_72," & _
"STKID_72,
TNXQTY_72, ORDNUM_72, UDFREF_72)" & _
" Values " & _
"('" & rowX.Item("LOTNUM_72").ToString & "'," & _
"#" & rowX.Item("TNXDTE_72") & "#," & _
"'" & rowX.Item("PRTNUM_72").ToString & "'," & _
"'" & rowX.Item("USRNAM_72").ToString & "'," & _
"'" & rowX.Item("STKID_72").ToString & "'," & _
"'" & rowX.Item("TNXQTY_72").ToString & "'," & _
"'" & rowX.Item("ORDNUM_72").ToString & "'," & _
"'" & rowX.Item("UDFREF_72").ToString & "')"

 

[John Vinson]

I have an Insert sql statement but am strugling with how not to have it
insert any duplicates LOTNUM_72 if already exists. Any help is
appreciated.

You can use a unique Index on LOTNUM_72, if that makes sense for the
rest of the application. You'll need to supress the warning messages
(by using the querydef Execute method and trapping and ignoring the
error, preferably).

What is RowX? Could it be defined in such a way that existing values
of LOTNUM_72 aren't available? Or could you use the BeforeUpdate event
of the control containing LOTNUM_72 to look up the value entered in
the table, and cancel if it's there?

John W. Vinson[MVP]