How to: Create Record Validation Rules
You use record-level validation rules when you want to control the type of information a user can enter in a database table record. You can also specify a custom message to display for invalid values instead of the default error message.
Note
Make sure that field validation rules do not conflict semantically with record validation rules. Visual FoxPro does not compare the field-level and record-level expressions for consistency.
To create a record validation rule
Open the database containing the table.
Open the table in the Table Designer.
In the Table Designer, click the Table tab.
In the Rule box from the Record validation area, type the validation expression you want. To build an expression, click the ellipsis (...) button.
When you are finished, click OK.
To display a custom error message, in the Message box, type the custom error message or expression you want to display for invalid values. To build an expression, click the ellipsis (...) button.
Note
Make sure to enclose message text in quotation marks ("").
When you are finished, click OK.
For more information, see Table Tab, Table Designer.
To create a record validation rule programmatically
When creating the table using the SQL CREATE TABLE command, include the CHECK clause. To include custom error message text, include the ERROR clause.
-OR-
To edit an existing table, open the table with the USE command and then use the SQL ALTER TABLE command with the CHECK or SET CHECK clause. To include custom error message text, include the ERROR clause.
For more information, see CREATE TABLE - SQL Command and ALTER TABLE - SQL Command.
Note
You cannot use the DBSETPROP( ) function to set record validation rules and error text; however, you can retrieve them using the DBGETPROP( ) function and the RuleExpression and RuleText table properties. For more information, see DBSETPROP( ) Function.
For example, suppose you want to make sure that new employees in an employee table are equal to or greater than 18 years old. The following code adds a record validation rule requiring that the hire date in the Hire_Date column is equal to or greater than the birth date plus 18 years using the SET CHECK clause. The ERROR clause specifies a custom error message:
ALTER TABLE Employee SET CHECK Hire_Date >= birth_date + (18 * 365.25) ;
ERROR "Employees must be 18 years or older by date of hire."
If the user enters an employee record with an invalid date, Visual FoxPro displays the custom error message you defined and does not update the record.
See Also
Tasks
How to: Create Field Validation Rules
How to: Remove Validation Rules
Concepts
Field and Record Validation Rules