Defining a Table Validation Rule for Web Databases

You can create table validation rules in web databases just like you can for client databases. 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 complete record. 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.

In the Restaurant Database you have been creating, we need an Appointments table to track our day to day appointments of managing the restaurant. This table requires a table validation rule. Click the Table button in the Tables group on the Create Ribbon tab to get started. Define that table now using the specifications in Table-6. Be sure to rename the ID field Access provides for you to AppointmentID and then save the table and name it Appointments. Be sure to also set both the StartTime and EndTime fields as required fields by selecting the Required option in the Field Validation group. You can set the Format property for the fields in the Format text box in the Formatting group on the Fields tab.

To define a table validation rule in a web database, open the table in Datasheet view, click the Validation button in the Field Validation group on the Fields contextual tab, and then click the Record Validation Rule option from the drop-down list.

Access opens the Expression Builder dialog box. For this web table, we want to ensure the start time of the appointment provided by the user comes before the end time. (It certainly would not make sense to have an appointment end before it even started.) We can accomplish this by using the less than (<) operator in a table validation rule. In the blank text box at the top of the Expression Builder dialog box, type [StartTime]<[EndTime] for the table validation rule. This table validation rule ensures that every record in the Appointments web table contains a start time that comes before the end time.

Click OK to save your new table validation rule and dismiss the Expression Builder dialog box. You should now add a descriptive message to display to users if they add data to a record that violates the rule in this web table. 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." Although the message does include the complete expression, it's not very pretty, is it? If you publish this web table to the server without a validation text, the default message you'll see on the server is, "List Data Validation failed," so we recommend you always add a descriptive validation text to accompany your table validation rules.

To create a table validation message, click the Validation button in the Field Validation group, and then click the Record Validation Message option from the drop-down list. You'll notice that Access adds an orange square around the graphic next to the Record Validation Rule option to indicate that you already defined a property for that specific option.

Access now opens the Enter Validation Message dialog box. Type the following custom message into the dialog box-The End Time for the appointment cannot be before the Start Time. Click OK to save your changes to this property and dismiss the dialog box. You now have a completed table validation rule and message for the Appointments web table. Access enforces this rule if you are adding or editing in client, and SharePoint enforces the rule after you publish the web database to the server. Be sure to click the Save button on the Quick Access Toolbar to save this latest change to your web table definition.