Use a validation rule instead of the Required property

Reader Štefan Masič provides today's Power Tip.

When you want to require users to enter a value in a field, a common technique is to set the Required property for that field to Yes (for example, tabPerson.Name.Required = Yes). However, when the user is then adding records to the table and neglects to insert value in that field, the built-in error message that appears is not very helpful:

"The field <TableName>.<FieldName> cannot contain Null value because the Required property is set to True. Enter a value in this field."

This error message is not appropriate for typical users, whether it's in English or any other language. Instead, I use another simple approach when building table properties:

1. Set Table.Field.Required = No
2. Set Table.Field.Validation Rule = IS NOT NULL
3. Set Table.Field.Validation Text = The value in field <Caption of field name> is required. Enter a value or select it from the list.

In this way, you can provide more specific information and helpful hints about how to enter the correct data, rather than just a "required" message.

Note: When converting an Access table into SQL Server we must consider this design and set this table property manually.

Send your Power Tips to Mike & Chris at accpower@microsoft.com.