Delete Trigger Error Msg not displaying in Access F/E

[PurpleUnicorn]

I have an Access database(B/E) that I am in the process of migrating to SQL Server. I currently have sample data in SQL Server and I am testing my Access F/E.

I have a delete trigger that is firing correctly - however, I am not getting the error message from my Access F/E.

Here is my trigger:
ALTER Trigger [CUSTOMERdelete] on [dbo].[CUSTOMER]
for delete
as
BEGIN
declare
@errorNumber int,
@errorMsg varchar(255)


if exists(
select * from deleted, ORDERS where ORDERS.CUSTOMER_ID = deleted.CUSTOMER_ID)
begin
select @errorNumber = 30004,
@errorMsg = 'Cannot delete from CUSTOMER because ORDERS exists.'
goto errorHandler
end

return
errorHandler:
raiserror @errorNumber @errorMsg
rollback transaction
END
GO

 

[mrdenny]

Your raiserror command isn't correct.

errorHandler:
   raiserror (@errorMsg, 16, 1)
   rollback transaction
END

You can only assign an error number if that error number exists in the sysmessages (or sys.messages if SQL 2005) table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[PurpleUnicorn]

Thanks for the reply Denny -

I changed my errorhandler - I am still only getting the Access generated error (2501) - not the error message defined in the trigger.
If I attempt to delete the record from the linked table - I get the correct message (from the trigger). When I attempt to delete the record from my customer form - I get the Access error message.

 

[mrdenny]

Check in the access forums. I'm not sure how to display SQL errors in Access. They may be able to provide more info.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com