Data Entry: Form Versus Substance

As the input of two-digit dates makes apparent, data entry is not necessarily the straightforward activity it appears to be. One reason is that what you enter might not be what you want displayed. For example, a U.S. telephone number has 10 digits (as in 2125551879), and that's usually all you want to have stored. At the same time, though, you want to see phone numbers displayed in a format such as [phone] or 212-555-1879.

In the case of a typical phone number, the dashes are just separators for convenience and display. In other cases, though, dashes could be a vital part of the value: An account number of 212-555-1879 might mean something completely different than 212-555-187-9. In this case, the dash is part of the value, and storing it is essential.

Another factor is that data entry can sometimes be made simpler by using a visual guide. When you enter a phone number in a paper form, for example, you might see three blank spaces enclosed within parentheses for your area code. Some users find such templates helpful for data entry in Access. But their use raises other issues related to data storage and display, as you'll presently see.

Input Masks

An input mask is a template for entering either text or dates. It can be set in the Input Mask property for the field in the lower pane of Table Design view. To create the most common masksfor example, for a phone number or a ZIP codeyou can use the Input Mask Wizard. The wizard is activated by first clicking in the Input Mask property and then clicking the three-dot button at the end of the row (see Figure 6.11). At its best, an input mask offers both easy entry and enhanced data integrity. (The case example at the end of the chapter shows you how to create a custom input mask, which will be available from the Input Mask Wizard whenever you need it.)

Figure 6.11. You must first click in the Input Mask property to see the three-dot button that activates the Input Mask Wizard.

NOTE

Input masks can also be set in a form. Right-click the text box of the field; choose Properties, Data tab; and enter a mask in the Input Mask property. You can also activate the wizard by clicking the three-dot button at the end of the property. As with other field properties, it's usually preferable to set a mask for a field in a table so that it is inherited when you create any form.

If you open the frmCustomers form, you'll find that the Fax Number field contains an input mask, which was inherited from the underlying table.

NOTE

When you opened the frmCustomers form, it was in Datasheet view. You can try the input mask in this view, or you can choose View, Form View (as shown in Figure 6.12). Both views use the same input mask, which shouldn't be surprising because (as I noted earlier) it's the same form.

Figure 6.12. The input mask in the Fax Number field helps maintain data integrity. Depending on the situation, however, it might make it more difficult to enter or edit the value.

To see the input mask at its best, click the New Record button, click in the Phone field, press Tab, and begin typing 2125556783 in the Fax Number field. Immediately a template appears that segregates the area code, exchange, and extension. You can type the 10 numbers of the phone number directly without paying attention to the parentheses and dashes. If you try to type an 11th number, you get a "boing" that tells you additional numbers are not allowed. Press Esc one or more times to delete your entry, and then click in the empty Phone field again.

The downside of an input mask can be seen by attempting to supply the fax number for ID 11, Ethel Scarberry. Go to the record and click anywhere in the middle of the Fax Number field (as you might ordinarily do when entering data quickly). Instead of seeing the cursor jump to the left, you're stuck in the middle of a template and have to begin navigating left (see Figure 6.12).

Input Mask Versus Format

Contrast the Fax Number field with the Phone field. This field has been formatted to display a phone number in the same format as the input mask you just saw without creating a mask (that is, the Input Mask property is blank). The Format property for CustPhone in the underlying table is (@@@) @@@-@@@@, with the @s standing for required characters. In addition, there's a validation rule of Like "##########" that requires users to enter 10 numbers.

Go to ID 10 for Justin Cotto and click anywhere in the Phone field. The cursor is positioned at the far left. Type 5675554822 and press Tab. Access stores only the 10-digit number but formats it like the fax number. The input mask might ensure that area code and exchange are entered properly, but this alternative method might make data entry simpler.

Let's make one more comparison. Suppose that you want to edit a specific number in the Fax Number field. You can click directly on the number that needs editing, and, with a little luck, it is highlighted. In contrast, if you click on a specific number in the maskless Phone field, the cursor is positioned between numbers, and you have to erase the number first. Close the frmCustomers form.

TIP

If you use both the Format and Input Mask properties for a single control, the setting in the Format property overrides the Input Mask property. This could present the best of all possible worlds: You can enter the data in the easiest way possible and display it as you like it. It can also be bewildering if the user doesn't know that both the Input Mask and Format properties have been set. You might want to include a control tip or status bar text to remind you (and other users) why the format changes when exiting the field.

An Additional Word on Formats

The brief discussions of the Format property here and in Chapter 5 do not adequately describe the range of issues this property raises. Most important, I have not discussed customized formats in any depth, nor the many symbols you can use to create them. I haven't provided these charts here, where their text cannot be copied and pasted. Instead, I suggest that, in Design view of any table, you click in the Format property for any field. Press F1 to see the symbols and examples of various formats in Access Help.

Q&A

Q1:

There's a question I've wanted to ask all chapter. Is there any way to print a blank form that can be filled out by hand?

A1:

The unrealistic answer is, yes. Theoretically, you could open the form in Form view and click New Record. Press the spacebar once in the first field. (Ignore AutoNumber fields.) Choose File, Print. Click Selected Record(s) and click OK. The realistic answer is, no. The procedure just described probably won't work because you've likely designed your table with required fields, validation rules, input masks, and so on. You'll have to do a good deal of acrobatics to get a completely blank form. Your best bet is to create a new form without any of these impediments that you use especially for printing.