• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Creating Your Database and Tables > Defining a Table Validation Rule

Defining a Table Validation Rule

The last detail to define is any validation rules that you want Microsoft Access 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
Field NameData TypeDescriptionField Size
ProductIDAutoNumberUnique product identifier 
ProductNameTextProduct description100
CategoryDescriptionTextDescription of the category50
TrialVersionYes/NoIs this a trial version? 
TrialExpireNumberIf trial version, number of days before expirationLong Integer

To define a table validation rule, be sure that the table is in Design view, and then click the Properties button on the toolbar or choose the Properties command from the View menu to open the Table Properties window, shown in Figure 4-27.

Figure 4-27. Defining a table validation rule.

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

On the Validation Rule line in the Table Properties window, you can enter any valid comparison expression, or you can use one of the built-in functions to test your table 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-27, I’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 Properties window.

To refer to a field name, enclose the name in brackets ([ ]), as shown in Figure 4-27. 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 22, “Understanding Visual Basic Fundamentals,” you must separate the arguments in a function with commas. Note that I said evaluation of the argument—this means I can enter additional tests, even another IIF, in the second and third arguments.

So, the first argument uses IIF to evaluate the expression [TrialVersion] = 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 in 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 fourth line in the Table Properties window, 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!

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint