Tables are the building blocks of an Access database. As you create each field in a table, you also assign properties to it. These properties include field size, format, validation rule, and other traits that help determine the substance and appearance of the data you enter.

Although tables are the foundation of your database, you usually use forms to enter data. The layout of a form makes it comparatively easy to input information, as well as to view the data on-screen.

When working with tables and forms, it is helpful to understand the relationship between the two. As a broad rule, a field in a form has the properties assigned to it in the table. For example, if the format of a Birthday field in an Employees table is Medium Date, it remains Medium Date in that field in a form.

There are, however, certain qualifications to this general rule that require some illustration. So let’s take a look at how field properties work in a form.

 Review Field Properties

Let’s use the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder to explore field properties. Open the file, and in the Database window, click the Tables button; click the Products table and then click Design View from the main menu.

Click somewhere in the UnitsInStock row. Review the Field Properties in the lower pane. Notice that the Format is General Number. Also note that while the field name is UnitsInStock, which is spelled solidly for better performance, the caption is Units In Stock, which is easier for users to read. Notice also that there is a Validation Rule: any value you enter must be greater than or equal to zero. Should you violate the rule when entering data (such as entering a negative number), Access will produce an error message that contains the warning text from the Validation Text field.

 Forms From Tables

Close the Products table. In the Database window, the Products table should still be selected. Click the Insert menu and Form. In the New Form dialog box, choose AutoForm: Columnar and click OK. Access creates a form based on the Products table.

Notice that the label of the UnitsInStock field is Units In Stock. Click the View button (in the top-left corner of the window; looks like a blue triangle or a database form) to switch to Design View. Right-click the Units In Stock label control and choose Properties. The Label: UnitsInStock_Label dialog box appears, and the Caption is Units In Stock, just as it was in the underlying table.

With the property sheet still open, right-click the UnitsInStock text box to the right of the Units In Stock control. Click the Data tab. Notice that the Control Source for the field is UnitsInStock. All the data for this text box comes from the UnitsInStock field in the Products table. Click the Format tab. The Format property is General Number, just as it was in the underlying table. Click in any open area to deselect the control and close the property sheet.

 Add Properties

Click View to return to Form View. Click in the UnitsInStock field for Chai, Product ID #1. Highlight 39, type 14, and press ENTER. Access edits the value to 14. Now let’s select 14 and type -4. Press ENTER, and Access tells you that you must enter a positive number. This is the same error message that was entered in the Validation Text property for the UnitsInStock field in the table. Clearly, the same Validation Rule, that the number must be greater than or equal to zero, also applies here. Highlight -4, type 39, and press ENTER to re-enter the original value.

Click the View button. Right-click the UnitsInStock text box and choose Properties. Click the Data tab. Notice that, although the Format property is General Number as in the underlying table, the Validation Rule and Validation Text properties are both blank. Nevertheless, when we tried to enter a negative value, Access enforced both properties for the field from the underlying table.

In the Validation Rule property, type >25. In the Validation Text property, type Units in stock must be greater than 25. Close the property sheet. Click the View button.

Click in the UnitsInStock field for Chai and select 39. Type 14 and press ENTER. Access won’t let you make the change at this point, and you get the error message you just entered in the property sheet. Change the value to 39 and press ENTER to re-enter the original value.

Close the form. Click Yes to save the design changes, and type Smart Computing Test as the name of the form (we’ll delete it in a moment).

 Edit Fields In A Table

In the Database window, click Tables. Open the Products table. For Product ID #1, scroll to the UnitsInStock field. Select 39 and type 14. Click the Record menu and choose Save Record. Access lets you save the record; the greater-than-25 rule in the form is not invoked in the table. Change the units in-stock for Chai back to 39 and press SHIFT-ENTER to save the record. Close the Products table. Click the Forms button, select the Smart Computing Test form, press DELETE, and confirm.

 Forms & Fields & FUN

Hopefully, these examples have given you a good idea of how property inheritance works in Access. When you are working with Forms and Fields and you get stumped, just remember a few key things. First, as a general rule, a field in a form inherits the field properties from the underlying table. A table, however, never inherits properties from a form based on it. Second, even though Validation Text and Validation Rule (as well as Default Value) properties are inherited by a field in a form, you can also enter properties specific to the form. In that case, Access will enforce these properties for the form alone. Third, it may save you a headache if you keep the properties between table and form consistent and avoid assigning different properties to the same field.

Now if only figuring out property inheritance from your mom’s cousin’s third wife’s great Uncle Joe were so simple.