Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  Other Development Tools  "Run Time Error 3167 - Record is Deleted" - error

Author  Topic  shidduch Starting Member 1 Post Posted - 2005-03-30 : 13:37:57 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]));