Constraints

Any single field can impose a number of constraints on data in that field when records are added or updated. These constraints are defined by a handful of properties. The AllowZeroLength property on a Text or Memo field indicates whether the field will accept a zero-length string. The Required property indicates whether some value must be entered in the field, or if it instead can accept a Null value. For a Field object in a Recordset object, these properties are read-only; their state is determined by the underlying table.

See Also For more information about how to set these properties on the Field objects of a TableDef object, or how to set them by using SQL DDL statements, see Chapter 3, “Data Definition and Integrity.”

Field-Level Data Validation

Validation is the process of determining whether data entered in a field’s Value property is within an acceptable range. A Field object in a Recordset object may have the ValidationRule and ValidationText properties set. The ValidationRule property is a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The ValidationText property is a string that’s displayed in an error message by Microsoft Access, or by the Data control in Visual Basic if you attempt to enter data in the field that is outside the limits of the ValidationRule property. If you’re coding from DAO directly, you can use the ValidationText property for a message you want to display to the user.

See Also For more information about setting table-level validation properties, see Chapter 3, “Data Definition and Integrity.”

Note The ValidationRule and ValidationText properties also exist at the Recordset object level. These are read-only properties, reflecting the table-level validation scheme established on the base table from which the current record is retrieved.

A Field object in a Recordset object also features the ValidateOnSet property. When this property is set to True, validation is checked as soon as the field’s Value property is set. If this property is set to False, validation is checked only when the completed record is updated. The default value is False. For example, if you’re adding data to a record that contains a large Memo or Long Binary (OLE Object) field and has a setting for the ValidationRule property, you should determine whether the new data violates the validation rule before attempting to write the data, so that you don’t waste time attempting to write an invalid record to disk. (Note that you should write the data when the field’s value is set, not when the entire record is written to disk.) To check validation when the field’s value is set, set the ValidateOnSet property to True.