Required Property

Microsoft Access Visual Basic

Show All

Required Property

   

You can use the Required property to specify whether a value is required in a field. If this property is set to Yes, when you enter data in a record, you must enter a value in the field or in any control bound to the field, and the value cannot be Null. For example, you might want to be sure that a LastName control has a value for each record. When you want to permit Null values in a field, you must not only set the Required property to No but, if there is a ValidationRule property setting, it must also explicitly state "validationrule Or Is Null".

Note   The Required property doesn't apply to AutoNumber fields.

Setting

The Required property uses the following settings.

Setting Visual Basic Description
Yes True (–1) The field requires a value.
No False (0) (Default) The field doesn't require a value.

You can set this property for all table fields (except AutoNumber data type fields) by using the table's property sheet or Visual Basic.

Note   To access a field's Required property in Visual Basic, use the DAO Required property.

Remarks

The Required property is enforced at the table level by the Microsoft Jet database engine. If you set this property to Yes, the field must receive or already contain a value when it has the focus — when a user enters data in a table (or in a form or datasheet based on the table), when a macro or Visual Basic sets the value of the field, or when data is imported into the table.

You can use the Required and AllowZeroLength properties to differentiate between information that doesn't exist (stored as a zero-length string (" ") in the field) and information that may exist but is unknown (stored as a Null value in the field). If you set the AllowZeroLength property to Yes, a zero-length string will be a valid entry in the field regardless of the Required property setting. If you set Required to Yes and AllowZeroLength to No, you must enter a value in the field, and a zero-length string won't be a valid entry.

Tip   You can use an input mask when data is entered in a field to distinguish between the display of a Null value and a zero-length string. For example, the string "None" could be displayed when a zero-length string is entered.

The following table shows the results you can expect when you combine the settings of the Required and AllowZeroLength properties.

Required AllowZeroLength User's action Value stored
No No Presses ENTER
Presses SPACEBAR
Enters a zero-length string
Null
Null
(not allowed)
No Yes Presses ENTER
Presses SPACEBAR
Enters a zero-length string
Null
Null
Zero-length string
Yes No Presses ENTER
Presses SPACEBAR
Enters a zero-length string
(not allowed)
(not allowed)
(not allowed)
Yes Yes Presses ENTER
Presses SPACEBAR
Enters a zero-length string
(not allowed)
Zero-length string
Zero-length string

If you set the Required property to Yes for a field in a table that already contains data, Microsoft Access gives you the option of checking whether the field has a value in all existing records. However, you can require that a value be entered in this field in all new records even if there are existing records with Null values in the field.

Note   To enforce a relationship between related tables that don't allow Null values, set the Required property of the foreign key field in the related table to Yes. The Jet database engine then ensures that you have a related record in the parent table before you can create a record in the child table. If the foreign key field is part of the primary key of the child table, this is unnecessary, because a primary key field can't contain a Null value.