how to not insert duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ricknicholson@att.net - 07 Apr 2006 20:20 GMT

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 & "')"

Reply to this Message

John Vinson - 08 Apr 2006 01:05 GMT

>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]

Reply to this Message