Before I discuss any data-entry techniques, I want to focus on how controls in forms inherit field characteristicsincluding field name, data type, and other field propertiesfrom the underlying table. This will give you a much better understanding of why the controls in forms look and act the way they do.
The object on the left in Figure 6.3 is a simple (and unrealistic) table in Design view that has a single field for each data type. For illustrative purposes only, I named each field to match the data type (as discussed in Chapter 5, some of these names are "reserved" words that should not be used for fields). The object on the right is a yet-to-be used AutoForm based on the table (you can quickly create an AutoForm by selecting the table in the Database window and choosing Insert, AutoForm). The labels in the form (AutoNumber, Text, Memo, and so on) match the field names in the underlying table.
Figure 6.3. A table and form that illustrate different data types.
Notice that the control to the right of each label directly reflects the data type, as follows:
AutoNumber, the primary key that increases automatically by one for each record and cannot be edited, is at its starting position of 1.
Text is allotted relatively little space (compared with Memo) because it holds a maximum of 255 alphanumeric characters.
Memo is awarded a chunk of space because presumably more data will be entered into a field with a data type that can accommodate thousands of characters.
Number has been given a default value of 0 and takes up relatively little space because a very large number can be entered in a small area.
Date/Time is also given little spaceyou don't need much room for a date.
Currency is formatted as a monetary value.
Yes/No is a check box that you select or deselect because the only two possible values are true and false.
OLE Object is given the most space of any control because it will likely display some kind of picture or document, for example. To enter an object, you select the box and choose Insert, Object.
Hyperlink is similar to Text. But you'll notice that if you start typing in it, the text appears underlined and in blue.
Field Properties in Tables and Forms
As a general rule, the controls in a form inherit the field properties that were set in the underlying table. For example, in the Nifty Lions database, there is a field in the Customers table called CustLastName (see Figure 6.4). In Field Properties in Table Design view, the Caption property entry is Last Name, so the title in the table column is Last Name. In the Customers form (which I've shown in Form view), the label for the CustLastName field matches that in the Caption of the underlying tablethat is, it will be Last Name.
Figure 6.4. The field name is CustLastName, but its caption (as shown in the Field Properties pane) is Last Name. The label in the form inherits the caption Last Name.
But Access also offers you the flexibility of setting control properties in a form for some of the same field properties that were set in a table. Setting the field property in the form has no impact on the field properties in the underlying table. Sometimes that added functionality in the form might be nice to have: For example, you could use a different caption for the field in a form than you used in the table. It also means, however, that field properties in a table and form can be in conflict. Let's see when and where that might be a problem.
Different Property Settings with No Conflict
First, let's take a quick look at setting a property in a form control where there is no potential for conflict.
Close tblCustomers and qryCustomers. Maximize frmCustomers (the Customers form).
Click View to switch to Design view.
Right-click the Last Name label and choose Properties, Format tab.
Edit the Caption label to Surname (see Figure 6.5). Close the property sheet.
Figure 6.5. The Last Name label control is selected and its property sheet is open. The Caption property has been edited to Surname.
On the View button, click the down arrow and choose Form View (or choose Form View from the View menu).
You'll see that the label has the new caption. This is an edit that doesn't cause any conflict with the field properties in the underlying table.
Click View to return to Design view.
Right-click the CustLastName text box (the control that contains the actual values for the field), and choose Properties, Data tab.
The Control Source remains CustLastName, the actual field name in the underlying table. Merely changing the label in the form has no effect on any of its values.
Close the property sheet. Close the form and save your changes.
If necessary, maximize the Database window. Open tblCustomers (the Customers table) in Datasheet view.
The caption for the CustLastName field remains Last Name, even though you changed the label in the form to Surname.
Close the table.
Different Property Settings in Conflict
Now we'll take a look at an example in which differences between field properties in the table and control properties in the form raise problems.
Open the tblMerchandise table in Design view. Click in the UnitsInStock field.
Note that Default Value is set at 1, and Validation Rule is <=15. That means each new record will have a value of 1 in this field, and the maximum value for the field is 15.
Close the table and open the frmMerchandise form.
This is a quick entry form created by selecting tblMerchandise in the Database window and choosing Insert, AutoForm. (The name of the form has been changed to frmMerchandise for consistency, and the form caption has been changed to Merchandise for simplicity.) Note that the label for the UnitsInStock field has inherited the Units In Stock caption from the underlying table.
Click View to switch to Design view.
Select the UnitsInStock text box. If the property sheet isn't open, press F4.
Click the Data tab and click in the Validation Rule property. Enter <20, an expression that will allow entries of 19 and below. Click in Validation Text and type Units In Stock must be fewer than 20.
Leave the property sheet open, and click View to return to Form view.
The current product should be Door Knocker. (If it isn't, right-click in the Product Name field, click in Filter For, type door knocker, and press Enter.) Move the property sheet if it's blocking your view. Click in the Units In Stock column, edit the value to 18, and press Tab. The text entered for the field in the Validation Rule of the table appears in the error message (see Figure 6.6). Click OK.
Figure 6.6. The error message is from the field property in the table.
Although your entry satisfied the validation rule in the form (<20), it did not satisfy the rule in the table (<=15). So it appears that the rule in the table takes precedence over the rule in the form. Or does it?
Click OK and edit Units In Stock for this record back to 3.
In the property sheet, edit Validation Rule to <8 and edit Validation Text to Units In Stock must be fewer than 8.
Click in the Units In Stock column, edit the entry to 12, and press Tab.
Now the error message matches that in the form. Even though the entry satisfies the condition in the table (<=15), Access won't let you violate the rule in the form (<8).
Click OK. Edit Units In Stock back to 3. Close the form and don't save your changes.
Summary of Property Inheritance
Obviously, having different validation rules for different data-entry conduits can cause inconsistencies in your data. Your best bet is to set the validation rule once in the table and then leave matters be. If you do find a need to set separate validation rules in a form, be sure you understand the implications for data integrity.
The conflicts between field properties in table and form could be further complicated by a validation rule set for the table as a whole. For example, you might have entered a validation rule in the table's property sheet that Units In Stock and Units On Hand could not exceed 25. Access will separately test each record you enter in the form to make sure this business rule is enforced.
What happens if you change a field property in a table after creating the form? Although the umbilical cord is torn, it is not entirely broken. Access enforces changes in some properties (including Validation Rule and Validation Text), but not necessarily in others.
The rules of property inheritance are not simple. Here is a summary of the major points:
A field inherits the Format, Decimal Places, Input Mask, and Caption properties from the underlying table. It inherits Status Bar Text from the field's Description. It also enforces the Default Value, Validation Rule, and Validation Text properties from the underlying table. The entries for these properties do not, however, appear on the controls' property sheets.
The settings in control properties in the form do not affect the field properties in the underlying table.
As a general rule, after a form has been created, control properties are unaffected by changes in field properties in the underlying table. The Default Value, Validation Rule, and Validation Text properties are exceptions to the general rule. Edits to these three properties are implemented in the form as well. This is also true for Validation Rule and Validation Text properties set for the table as a whole.
Access 2003 enables you to update certain field properties (such as Format) in forms and reports throughout your database when you edit the property in an underlying table. The Show Property Update Options buttons option must first be selected on the Tables/Queries tab of the Options dialog box, available from the Tools menu. When you edit a field property in the table, the Update Properties dialog box appears (you might have to select the option from the smart tag that appears). You can select the objects where you want the update propagated.
If you set a validation rule for a field in both the table and the form, Access implements both rules. Because both rules apply, whichever one is the more restrictive is the de facto validation rule.
If you set a default value for a field in a form, it overrides the default value in the table.