From: Mark A. Sam on
Hello,

I'm trying to Insert a record from a table into itself. The problem is
field [ordDetID] which is a Primary Key, autonumber field. If I leave it in
the statement, I get a key violation error, because it is trying to copy the
existing field values. If I remove the field, I get an SQL insert error
(the back end is SQL Server).

INSERT INTO [Order Entry ST Products] ( ordID, ordDetID, detItem, detQty,
detProdCode, detProdDescription, detProdNotation, detPrice, detTax, detExt,
DetProdLocation )
SELECT getnewnum() AS ordID, [Order Entry ST Products].ordDetID, [Order
Entry ST Products].detItem, [Order Entry ST Products].detQty, [Order Entry
ST Products].detProdCode, [Order Entry ST Products].detProdDescription,
[Order Entry ST Products].detProdNotation, [Order Entry ST
Products].detPrice, [Order Entry ST Products].detTax, [Order Entry ST
Products].detExt, [Order Entry ST Products].DetProdLocation
FROM [Order Entry ST Products]
WHERE ((([Order Entry ST Products].ordID)=setPubOrdID()));

Any help on how to redesign the query to work is appreciated.

God Bless,

Mark A. Sam