Microsoft KB Archive/275057

= ACC2002: Trigger That Contains a User-Defined Error Message Cannot Be Raised =

Article ID: 275057

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q275057



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
A trigger that is assigned to a table does not display an error message when the trigger is executed.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
The following steps assume that you have access to a computer that is running Microsoft SQL Server 7.0 or later.  Create a new Access project that is based on the Pubs sample database that ships with SQL Server.  Create the following new table in the project:   Table: Table1 ---  Column Name: RecordID Data Type: int Length: 4 Allow Nulls: false

Column Name: RecordInfo Data Type: Char Length: 50 Allow Nulls: true

Table Properties: Table1 PrimaryKey: RecordID   Save the table as Table1, and then create a new record as follows:   Table: Table1 ---  Column Name: RecordID Column Value: 1

Column Name: RecordInfo Column Value: My Info  Close the table. Right-click Table1 in the Database window, and then click Triggers on the shortcut menu.</li> When the Triggers for Table: Table1 dialog box appears, click New.</li>  In the Table1_Trigger1 : Table1 window, type or paste the following Transact-SQL: CREATE TRIGGER Table1_Trigger1 ON dbo.Table1 FOR Update As RAISERROR('You cannot edit this field', 0, -1) Rollback Transaction </li> On the File menu, click Save. Click OK to save the trigger with the name of Table1_Trigger1, and then close the Trigger window.</li> Open the Table1 table in Datasheet view, and then edit the record. For example, change &quot;My Info&quot; to &quot;Your Info,&quot; and move your mouse pointer to another record.</li></ol>

Note that the trigger is fired and the modification to the record is rolled back as expected, but the error message is not displayed.

This problem also occurs when you use ActiveX Data Objects (ADO), as illustrated in the following example. <ol> Complete steps 1 through 8 in the first example, and close the Table1 table if it is still open.</li>  ON the Insert menu, click Module, and then type or paste the following code into the module:

Note In the following sample code, you must change user id=  and password=  to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database. Option Compare Database Option Explicit

Sub subTest Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset con.Provider = &quot;MSDataShape&quot; con.Open &quot;Data Provider=sqloledb.1;pwd= ;uid= ;initial &quot; & _ &quot;catalog=pubs;data source=<ServerName>&quot; 'In the command line above, replace <ServerName> with the actual name 'of your SQL Server. 'Additionally, if your SQL Server is set up to use NT Integrated 'security only, you must remove &quot;pwd= ;uid= &quot; and add '&quot;Integrated security=sspi&quot; to the connection string.

rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockOptimistic rs.Open &quot;SHAPE {SELECT * FROM Table1} AS rsLevel0&quot;, con rs.MoveFirst rs!RecordInfo = &quot;Your Info&quot; rs.Update

rs.Close con.Close Set rs = Nothing Set con = Nothing

MsgBox &quot;Done&quot; End Sub </li> On the View menu, click Immediate Window, type subTest in the Immediate window, and then press ENTER.</li> Click OK to the message indicating that the code has finished running, and then open the Table1 table in Datasheet view.</li></ol>

Note that the trigger prevented the update as you intended, but you still did not receive the error message from the trigger.

<div class="references_section">