Extend Microsoft Access Applications to the Cloud: Creating a Blank Web App and Using Templates

Adding validation rules

A web app has validation features similar to a desktop database, but it uses a different set of functions to support validation. Each table is allowed a validation rule and message. The table-level validation rule is used to cross-validate data and compare the values in several fields. Depending on the data type, each field can have a separate validation rule, which normally refers only to the field but can make references to the values in other fields. (This is a feature that is not available in desktop database field validation rules.)

In the sample web app in this chapter, look at the template table Customers and click the Validation Rule button on the Design contextual ribbon tab. You will see the rule displayed in the Expression Builder dialog box, as shown in Figure 4-15.

FIGURE 4-15 Expression Builder dialog displaying a validation rule.

TIP

In addition to the help information provided within Access, the validation rule functions (such as COALESCE, which returns the first NOT NULL value from a list of choices) are SQL Server functions.) You can find detailed examples and further information when looking in the SQL Server help; this information is most easily found either by searching online or by using the help features in the SQL Server Management Studio (SSMS), which is described in Chapter 9.

The preceding example of a table-level validation rule ensures that one of the four specified fields has to contain a value:

- Coalesce([First Name],[Last Name],[Email],[Company]) Is Not Null

Individual fields can also have a single validation rule. For example, in the Products table, if you want to ensure that each product code starts with the string “01-“, you could add the validation rule shown in Figure 4-16.

FIGURE 4-16 Field validation rule and text.

In this example, you find the rule Left([Product Code],3) = "01-", which checks that each product code starts with “01-“. When entering the rule, you can choose to use single quotes (such as ‘01-’), which would also be accepted.

TIP

To delete a field or table validation rule or message, display the text and remove the text or rule using the backspace key.