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


The information in this article applies to:

  • Microsoft Access 2002
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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample project NorthwindCS.adp.
  2. Open the Employees table in Design view.
  3. Right-click the table, and then click Constraints on the shortcut menu.
  4. Modify the check constraint so that it reads as follows:
    (DATEPART(year, [BirthDate]) < DATEPART(year, getdate()))
  5. Enter validation text for this constraint, for example, Employee birth year must be less than today's year!.
  6. Close the property sheet, and then save the design changes to the table.
  7. 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.
  8. Open the Employees table in Design view.
  9. Right-click the table, and then click Constraints on the shortcut menu.
  10. Remove the validation text for the constraint.
  11. Close the property sheet, and then save the design changes to the table.
  12. 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).
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbbug kbnofix KB287488
©2003 Microsoft Corporation. All rights reserved.