Microsoft KB Archive/287488

= ACC2002: Removing Validation Text Causes a Record That Cannot Be Committed When the Constraint Is Violated =

Article ID: 287488

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287488



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

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



SYMPTOMS
When you add and then remove constraint validation text, a new record does not return a SQL Server error message when the record violates the constraint. In addition, you are unable to move off a new record when it violates the constraint after you add and then remove the constraint validation text. To move to a different record, you must undo the record that is violating the constraint.



CAUSE
When you delete the validation text, the validation text becomes an empty string instead of actually being deleted.



RESOLUTION
To work around this behavior, use one of the following methods:
 * Delete the current constraint, and then add a new constraint.
 * Add the validation text back to the previously created constraint.
 * Drop the extended property by using sp_dropextendedproperty.



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 the Behavior
 Open the sample project NorthwindCS.adp. Open the Employees table in Design view. Right-click the table, and then click Constraints on the shortcut menu.  Modify the check constraint so that it reads as follows: (DATEPART(year, [BirthDate]) < DATEPART(year, getdate))  Enter validation text for this constraint, for example, Employee birth year must be less than today's year! .</li> Close the property sheet, and then save the design changes to the table.</li> In Datasheet view, insert a new record into the Employees table. For the BirthDate and HireDate fields, use the current date. Try to move off this record. Note that the check constraint returns the validation text. Delete the new employee record.</li> Open the Employees table in Design view.</li> Right-click the table, and then click Constraints on the shortcut menu.</li> Remove the validation text for the constraint.</li> Close the property sheet, and then save the design changes to the table.</li> In Datasheet view, insert a new record into the Employees table. For the BirthDate and HireDate fields, use the current date. Try to move off this record. Note that you do not receive a SQL Server error message and that you cannot change focus to a different employee record until this record is undone (by using Edit, Undo Current Field/Record, or CTRL+Z).</li></ol>

Additional query words: pra extended property constraint validation text blank empty locked undo record

Keywords: kbbug kbnofix KB287488

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.