Defining a Table Validation Rule

The last detail to define is any validation rules that you want Access 2007 to apply to any fields in the table. Although field validation rules get checked as you enter each new value, Access checks a table validation rule only when you save or add a row. Table validation rules are handy when the values in one field are dependent on what’s stored in another field. You need to wait until the entire row is about to be saved before checking one field against another.

One of the tables in the Contact Tracking database-Products-needs a table validation rule. Define that table now using the specifications in Table 4–7. Be sure to define ProductID as the primary key and then save the table and name it Products.

Table 4–7: Field Definitions for the Products Table
Open table as spreadsheet

Field Name	Data Type	Description	Field Size
ProductID	AutoNumber	Unique product identifier
ProductName	Text	Product description	100
CategoryDescription	Text	Description of the category	50
UnitPrice	Currency	Price
TrialVersion	Yes/No	Is this a trial version?
TrialExpire	Number	If trial version, number of days before expiration	Long Integer

To define a table validation rule, be sure that the table is in Design view, and then click the Property Sheet button in the Show/Hide group of the Design contextual tab on the Ribbon, shown in Figure 4–23.

Figure 4–23: You can define a table validation rule in the property sheet for the table.

To learn more about expressions, see “Using the Expression Builder” on page 371.

On the Validation Rule line in the table’s property sheet, you can enter any valid comparison expression, or you can use one of the built-in functions to test your table’s field values. In the Products table, we want to be sure that any trial version of the software expires in 30, 60, or 90 days. Zero is also a valid value if this particular product isn’t a trial version. As you can see in Figure 4–23, we’ve already entered a field validation rule for TrialExpire on the General tab to make sure the TrialExpire value is always 0, 30, 60, or 90-In (0, 30, 60, 90). But how do we make sure that TrialExpire is zero if TrialVersion is False, or one of the other values if TrialVersion is True? For that, we need to define a table-level validation rule in the table’s property sheet.

To refer to a field name, enclose the name in brackets ([]), as shown in Figure 4–23. You’ll use this technique whenever you refer to the name of an object anywhere in an expression. In this case, we’re using a special built-in function called Immediate If (or IIF for short) in the table validation rule to perform the test on the TrialExpire and TrialVersion fields. The IIF function can evaluate a test in the first argument and then return the evaluation of the second argument if the first argument is true or the evaluation of the third argument if the first argument is false. As you will learn in Chapter 19, “Understanding Visual Basic Fundamentals,” you must separate the arguments in a function call with commas. Note that we said evaluation of the argument-this means we can enter additional tests, even another IIF, in the second and third arguments.

In the Products table, you want to make sure that the TrialVersion and TrialExpire fields are in sync with each other. If this is not a trial version, the TrialExpire field value should be zero (indicating the product never expires), and if it is a trial version, TrialExpire must be set to some value greater than or equal to 30. The expression we used to accomplish this is as follows:

• IIf([TrialVersion]=True,[TrialExpire]>=30,[TrialExpire]=0)

So, the first argument uses IIF to evaluate the expression [Trial Versi on]=True is the value in the field named TrialVersion True? If this is true (this is a trial version that must have a nonzero number of expiration days), IIF returns the evaluation of the second argument. If this is not a trial version, IIF evaluates the third argument. Now all we need to do is type the appropriate test based on the true or false result on TrialVersion. If this is a trial version, the TrialExpire field must be 30 or greater (we’ll let the field validation rule make sure it’s exactly 30, 60, or 90), so we need to test for that by entering [TrialExpire] >= 30 in the second argument. If this is not a trial version, we need to make sure TrialExpire is zero by entering [TrialExpire]=0. in the third argument. Got it? If TrialVersion is True, then [TrialExpire] >= 30 must be true or the validation rule will fail. If TrialVersion is False, then [TrialExpire]=0. must be true. As you might imagine, once you become more familiar with building expressions and with the available built-in functions, you can create very sophisticated table validation rules.

On the Validation Text line of the table’s property sheet, enter the text that you want Access to display whenever the table validation rule is violated. You should be careful to word this message so that the user clearly understands what is wrong. If you enter a table validation rule and fail to specify validation text, Access displays the following message when the user enters invalid data: “One or more values are prohibited by the validation rule ‘< your validation rule expression here >’ set for ‘<table name>’ Enter a value that the expression for this field can accept.”

Not very pretty, is it? And you can imagine what the user will say about your IIF expression!