"Run Time Error 3167 - Record is Deleted" - error

[ecomajor]

Hi,
I have an insert statement that in MS ACCESS 2003. This statement works when the tables are in access however, I have linked the tables to SQL Server 2000 and that is when I get issues. This code is called from an access form. I am getting the error only when one of the values for color or size is null. so I figured I would use IIF(isnull(x),"NULL",x) statements so that there would be no null values. However, this does not seem to work.
But it still gives me this error when I try pulling rows with the null values. If you have any ideas please email them to me.
Here is the code for it

INSERT INTO tblActivity ( OrderID, ActivityDate, Type, ItemID, ActivityPrice, SalesTax, ItemInvoiceDisplay, EnteredDate, EnteredBy, CustInvoice )
SELECT Forms!Invoices!InvoicePOID AS Expr5, Forms!Invoices!OrderDate AS Expr6, "Invoiced" AS Expr4, tblItems.ItemID, IIf(Forms!Invoices!CType='Retail',[itemrprice],IIf(Forms!Invoices!CType='Wholesale',[itemwprice],[itemwprice2])) AS Expr1, RoundMe(IIf(Forms!Invoices!CType='Retail',[itemrprice],IIf(Forms!Invoices!CType='Wholesale',[itemwprice],[itemwprice2]))*Forms!Invoices!CSalesTax) AS Expr3, [Desc] & ": " & [CatName] & " (" & [Color] & ", " & [Size] & ")" & IIf([stillcustom]='Y',' <' & [customcustomer] & '>') AS Expr2, Now() AS Exmmmpr4, WhoUser() & "/" & WhoComputer() AS Exzzzzzpr5, tblItems.StillCustom
FROM tblCategories INNER JOIN ((tblItems LEFT JOIN tblSizes ON tblItems.HairLengthID = tblSizes.BSID) LEFT JOIN tblColors ON tblItems.ColorID = tblColors.ID) ON tblCategories.CatID = tblItems.CatID
WHERE (((tblItems.ItemID)=[Forms]![Invoices]![SelectItem]));

 

[SQL_Hell]

Ok here goes........


Is this insert being called as a query (or view) or as a runsql command in VBA?

you will get a much better response in the SQL server forum as there are lots of people in there who use sql server and access.