Designing Forms for Efficient and Accurate Data Entry

This chapter is from the book

Microsoft Office Access 2007 Forms, Reports, and Queries

Learn More Buy

This chapter is from the book

This chapter is from the book 

Microsoft Office Access 2007 Forms, Reports, and Queries

Learn More Buy

Using Controls to Limit Data Entry Choices

Data entry always trips over two unfortunate facts of life: Humans are fallible creatures, and typing is an error-prone activity. Expert data entry operators can't achieve 100% accuracy (although some come remarkably close), and the rest of us can only hope for the best. In short, if your form relies on other people (or yourself, for that matter) typing in field values, it's death-and-taxes certain that your table will end up with errors.

It stands to reason, then, that you can greatly reduce the number of errors by greatly reducing the amount of typing. The best way to do that is by taking advantage of controls to generate field values automatically. Here are some examples:

• If you have a Yes/No field that uses a text box, the users must enter the unintuitive values -1 (for Yes) and 0 (for No). A more intuitive approach is to use a check box (or toggle button) that the users either activate (for Yes) or clear (for No).
• Suppose you have a field that can take only one of a small set of values (say, two to five values). For example, an invoice form might offer the users three choices for freight or four choices for credit cards. Again, instead of having the users type the freight choice or credit card name, you can populate the form with option buttons representing the choices.
• Suppose you have a field that can take one of a relatively large set of values (more than five). For example, the field might hold a customer name or a product name. Instead of making the users look up (time-consuming) and then type (inaccurate) the value, it's both faster and more accurate to place all the possible values in a drop-down list.

The rest of this chapter shows you how to use check boxes, toggle buttons, option buttons, lists, and other controls to build faster and more accurate forms. In each case, the idea is to move the users away from typing values and toward selecting them via a familiar and easily used control.

Working with Yes/No Fields

You use Yes/No fields in tables when you have a quantity that you can represent in one of two states: on (Yes, True, or -1) or off (No, False, or 0).

When you create a Yes/No field in the table Design view, the Display Control property (it's in the Lookup tab) defaults to Check Box. This means that when you add a Yes/No field to a form, Access automatically represents the field with a check box control (along with a label that displays the name of the field or the field's Caption property). However, it's possible that the Display Control property has been set to Text Box, either by design or by accident. As I mentioned earlier, you want to avoid users having to enter -1 or 0 into a text box, so you should never use a text box for a Yes/No field on your forms. Instead, you have two choices:

• If you have access to the table's design, change the Yes/No field's Display Control property to Check Box. After you've done that, return to the form, delete the Yes/No field's text box and label (if they're already on the form), and then add the field back to the form to get the check box version.
• If you can't change the table design, use a check box or toggle button control bound to the Yes/No field. The next two sections show you how to do this.

Using Check Boxes

Here are the steps to follow to insert a check box and bind it to a Yes/No field:

1. In the Design tab's Controls group, click the Check Box button.
2. Draw the check box on the form.
3. Edit the text of the label control that Access adds to the right of the check box. (For clarity, it's best to use the name of the Yes/No field.)
4. Click the check box and then choose Design, Property Sheet to open the Property Sheet pane.
5. In the Data tab, use the Control Source property to choose the name of the Yes/No field you want bound to the check box.
6. In the Default Value property, enter the initial value for new records: Yes, True, or -1; or No, False, or 0.

It's worth pointing out here that check boxes (and toggle buttons, discussed next) can insert only one of two values into a field: -1 or 0. You can't use a check box for other two-state choices, such as "male" and "female" or "Pepsi" and "Coke". For fields that can take only one of two values other than 0 and -1, use option buttons instead (as described later in this chapter).

Using Toggle Buttons

A toggle button is a cross between a check box and a command button: Click it once, and the button stays pressed; click it again, and the button returns to its normal state. The button can display either a caption or a picture. Here are the steps to follow to insert a toggle button and bind it to a Yes/No field:

1. In the Design tab's Controls group, click Toggle Button.
2. Draw the toggle button on the form.
3. Choose Design, Property Sheet to open the Property Sheet pane.
4. In the Format tab, you have two choices that determine what appears on the face of the button:
   • Caption—Use this property to specify text that appears on the face of the button. (For clarity, it's best to use the name of the Yes/No field.)
   • Picture—Use this property to specify an image that appears on the button face. Click the ellipsis button (...) to display the Picture Builder dialog box, shown in Figure 3.5. Either use the Available Pictures list to click an image or click Browse to choose an image from the Select Picture dialog box (although note that Access can only use BMP or icon files).

   CAUTION

   If you want to use a custom picture, bear in mind that if the image is larger than the toggle button, Access won't shrink the image to fit inside the button—it just centers the image in the button and displays as much as will fit. Therefore, always choose a bitmap or icon that's the same size or smaller than the toggle button.

   

   Figure 3.5 Use the Picture Builder dialog box to choose an image to appear on the face of the toggle button.

5. In the Data tab, use the Control Source property to choose the name of the Yes/No field you want bound to the toggle button.
6. In the Default Value property, enter the initial value for new records. For the "pressed" state, use Yes, True, or -1; for the "unpressed" state, use No, False, or 0.

Using Option Buttons to Present a Limited Number of Choices

Option buttons are a good choice if the underlying field accepts only a limited number of possible numbers: at least two but no more than about five or six. (If you have more possible values, use a list box or combo box, discussed later in this chapter.)

How does having multiple option buttons on a form enable you to store a single value in a field? There are two components to consider:

• The option buttons—You assign each option button a value from among the list of possible values that the field can take.

  NOTE

  Option button values must be numeric. Therefore, you can use option groups and option buttons only with numeric fields.

• The option group—This is a separate control that you use to organize the option buttons. That is, if you insert multiple option buttons inside a group, Access allows the users to activate only one of the options at a time. (You can also use check boxes or toggle buttons, but option buttons are best because most users are familiar with them and know how to operate them.)

The option group is bound to the field in the underlying table. Therefore, when you activate an option button, the value assigned to that button is stored in the field. This form of data entry brings many advantages to the table (literally!):

• It's quick. The users don't have to look up the possible values elsewhere.
• It's accurate. The field value is stored "behind the scenes," so the users can't enter the wrong value.
• It's intuitive. The option button captions can be as long as you like (within reason), so you can provide users with a helpful description or title for each option.
• It's familiar. All Windows users know how to operate option buttons, so no extra training is required.

The next two sections show you how to create option buttons using a wizard and by hand.

Running the Option Group Wizard

The easiest way to create an option group and its associated option buttons is to use the Option Group Wizard, as described in the following steps:

1. In the Design tab's Controls group, make sure the Control Wizards button is activated and then click the Option Group button.
2. Draw the option group on the form. Access launches the Option Group Wizard.
3. For each option button you want, type the label in the Label Names list and press Tab. When you're done, click Next.
4. To select a default choice (the option that Access activates automatically when the user starts a new record), leave the Yes, The Default Choice Is option activated and then choose the option label from the list. Click Next.
5. Use the Values column to assign a numeric value for each option, as shown in Figure 3.6. Note that each value must be unique. Click Next when you're done.
   

   Figure 3.6 Use this Option Group Wizard dialog box to assign a unique numeric value to each option.

6. Specify where you want the option group value stored (click Next when you're done):
   • Save the Value for Later Use—Click this option to have Access save the option group value. This is mostly used by VBA programmers—the current value of the option group is stored in the Frame object's Value property.
   • Store the Value in This Field—Click this option and then select a field from the list to have Access store the option group value in the field.
7. Click the type of control you want to use in the option group: Option Buttons, Check Boxes, or Toggle Buttons. You can also select the special effect used by the option group border (Etched, Flat, and so on). Click Next to continue.
8. Edit the option group caption (the text that the users see along the top border of the option group frame; use the field name or something similar) and then click Finish to complete the wizard.

Creating an Option Group By Hand

If you'd rather create the option group yourself, here are the steps to follow:

1. In the Design tab's Controls group, make sure the Control Wizards button is deactivated and then click the Option Group button.
2. Draw the option group on the form.
3. In the Design tab's Controls group, click Option Button.
4. Draw the option button inside the option group.
5. Choose Design, Property Sheet to display the option button's property sheet.
6. In the Data tab, use the Option Value property to specify the numeric value associated with the option.
7. Use the drop-down list to choose the label associated with the option button. (It's the control that is one number greater than the option button. For example, if the option button name assigned by Access is Option10, the associated label will be named Label11.)
8. In the Format tab, use the Caption property to specify text that appears alongside the option button.
9. Repeat steps 3–8 for the other option buttons you want to add to the option group.
10. Use the drop-down list to choose the option group (it's named Framen, where n means it was the nth control added to the form).
11. In the Data tab, use the Control Source property to choose the field in which you want the value of the selected option button stored.
12. If you want one of the option buttons to be activated when the users start a new record, use the Default Value property to enter the value of the corresponding option button.
13. Close the property sheet.

Case Study: Using an Option Group to Select the Shipper

In the Northwind 2007 database, the Orders table has a Shipper ID field that specifies which shipping company to use. There are three shipping companies that the users can select: Shipping Company A, Shipping Company B, and Shipping Company C. (The person at Microsoft who put together the Northwind 2007 database was singularly uncreative when it came to names.) Option buttons can take only numeric values, so you can't use them to assign a text value such as "Shipping Company A" to the Shipper ID field. That's not a problem because the Shipper ID field is designed to store a number: 1 for Shipping Company A, 2 for Shipping Company B, and 3 for Shipping Company C. These numbers correspond to the ID field in the Shippers table. The Shipper and Orders tables have a one-to-many relation based on the ID and Shipper ID fields.

A field that takes one of three numeric choices is perfect for an option group. You set things up as follows:

• Create an option group and bind it to the Shipper ID field.
• Add three option buttons for Shipping Company A, Shipping Company B, and Shipping Company C, and assign them the values 1, 2, and 3, respectively.

Figure 3.7 shows the resulting option group in the form.

Figure 3.7 This form uses an option group to choose the shipping method for each order.

Using Lists to Present a Large Number of Choices

Option buttons have three main disadvantages:

• If a field can take more than about five or six values, option buttons become too unwieldy and confusing for the users.
• Option buttons can't work with non-numeric values.
• Users can't enter unique values. This is normally a good thing, but in some instances you might want to give the users the flexibility to choose either a predefined value or to enter a different value.

To solve all these problems, Access offers two different list controls that enable you to present the users with a list of choices:

• A list box presents a list of choices. These choices are static, meaning that users can't enter any different values.
• A combo box enables users to either select a value from a drop-down list or (optionally) enter a different value using the associated text box.

In both cases, the item the users choose from the list (or the item the users enter in the combo box) is the value that is stored in the bound field. This means that you can use list and combo boxes for any type of value, including numeric, string, and date values.

It's important to note that Access defaults to a combo box when you add to the form a field that is used as part of a relationship with another table. Specifically, if the relationship is one-to-many and the current table is the "many" side, adding the field that corresponds to the common field on the "one" side creates a list that contains all the values from that field.

For example, the Products table has a one-to-many relationship with the Order Details table via the common ID and Product ID fields, respectively. If you're putting together a form based on the Order Details table and you add the Product ID field, Access creates a combo box list and populates it with the values from the Products table's Product Name field. Why Product Name and not Product ID? The reason is that in the design for the Order Details table, the Product ID field's Row Source property (in the Lookup tab) specifies an SQL statement that selects the Product Name field from the Products table:

SELECT ID, [Product Name] FROM Products ORDER BY [Product Name]

The next few sections show you various ways to work with both controls.

Starting the List Box or Combo Box Wizard

The List Box Wizard and Combo Box Wizard make it easy to create a bound list control. Here are the steps to follow to get started with these wizards:

1. In the Design tab's Controls group, make sure the Control Wizards button is activated.
2. Click either Combo Box or List Box.
3. Draw the box on the form. Access starts either the List Box Wizard or the Combo Box Wizard.

These wizards work identically, but the steps you take vary dramatically depending on which option you choose in the initial dialog box. The next three sections take you through the details of each option.

Getting List Values from a Table or Query Field

The most common list scenario is to populate the list box or combo box with values from a field in a specified table or query. For example, if you're putting together an orders form, you'll probably want to include a list that contains all the customer names, so you'll populate the list with the values from the Customers table's Company field.

The following steps show you how to continue with the List Box or Combo Box Wizard to populate a list with values from a table or query field:

1. In the first wizard dialog box, click the I Want the List Box to Look Up the Values in a Table or Query option and then click Next.
2. Click the table or query that contains the field you want to use for the list and then click Next.
3. In the Available Fields list, select the field you want to use and then click > to add it to the Selected Fields list. Click Next.
4. If you want the list sorted, use the drop-down list to choose the field you selected, click the Ascending (or Descending) toggle button, and then click Next.
5. Click and drag the right edge of the column header to set the width of the list column and then click Next.
6. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.
7. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Specifying Custom List Values

If the items you want to appear in your list don't exist in another table or query, you need to specify them by hand. Here are the steps to follow to continue with the List Box or Combo Box Wizard and populate a list with custom values:

1. In the first wizard dialog box, click the I Will Type in the Values That I Want option and then click Next.
2. For each value you want to add, type the item text and press Tab. Click Next when you're done.
3. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.
4. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Getting List Values from the Current Table

Sometimes the values you want in your list already exist in the form's underlying table or query. For example, if your form uses the Customers table, you might want to set up a list for the Job Title field and use the unique values in that to populate the list. (This example illustrates when you might want to use a combo box, because a new customer contact could have a title other than the ones in the list.) Note, however, that the list you create using this method will always be an unbound control.

The following steps show you how to continue with the List Box or Combo Box Wizard to populate a list with values from a field in the form's current data source:

1. In the first wizard dialog box, click the Find a Record on My Form Based on the Value I Selected in My Combo Box option and then click Next.
2. In the Available Fields list, select the field you want to use and then click > to add it to the Selected Fields list. Click Next.
3. Click and drag the right edge of the column header to set the width of the list column and then click Next.
4. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Creating a Multiple-Column List

Sometimes displaying a single column of values in a list might not be enough. For example, if you're working with data from the Northwind 2007 Products table, displaying just the Product Name field might not give the users enough information. Instead, you might also want to show the users the corresponding Category or Supplier value (using an inner join query for the latter) for each product.

You can do this by adding one or more columns to the list and then specifying which of those columns contains the value you want to store in your form's bound field. Here are the steps to follow:

1. Draw a list box or combo box on the form to launch the List Box or Combo Box Wizard.
2. In the first wizard dialog box, select the I Want the List Box to Look Up the Values in a Table or Query option and then click Next. (Note that you can also display multiple columns using the Find a Record on My Form Based on the Value I Selected in My Combo Box option.)
3. Select the table or query that contains the field you want to use for the list and then click Next.
4. In the Available Fields list, for each field you want to display in the list, select the field and then click > to add it to the Selected Fields list. Click Next.
5. You sort the list on multiple fields by using separate drop-down lists to choose each field and its sort order. Click Next.
6. Click and drag the right edge of each column header to set the width of the list columns. Note, too, that you can also change the column order by clicking and dragging the column headers left or right. Click Next.
7. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.
8. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Figure 3.8 shows a form that uses a two-column combo box to display both the Product Name field and the Category field from the Products table.

Figure 3.8 This combo box uses multiple columns to display both the Product Name field and the corresponding Category field.

Modifying List Box and Combo Box Properties

If you want a bit more control over the list layout and data, you need to tweak the control properties. Here's a list of the properties to work with:

• Control Source (Data tab)—The field in which the selected list item will be stored.
• Row Source Type (Data tab)—Choose Table/Query for values that come from a table or query field; choose Value List for values that you enter by hand; choose Field List to populate the list with field names from a table or query.
• Row Source (Data tab)—This value depends on the Row Source Type value:
  • Table/Query—Enter an SQL SELECT statement that specifies the field you want to use to populate the list (along with any criteria you want to use). Alternatively, click the ellipsis button (...) and use the Query Builder to specify the table, field, and criteria. When you close the Query Builder, Access converts your selections into an SQL SELECT statement.
  • Value List—Enter the values with which you want to populate the list, separated by semicolons.
  • Field List—Enter the name of the table or query that contains the field names with which you want to populate the list.

  NOTE

  If you want to display a multiple-column list, specify each field that you want to include in the list after the SELECT verb in the SQL statement, as in this example:

            SELECT CategoryName, Description FROM Categories;
  

  Alternatively, use the Query Builder to add each field to the criteria grid.

• Bound Column (Data tab)—If Row Source Type is Table/Query and the Row Source SELECT statement specifies only a single field, the Bound Column value should always be 1. If the Row Source proeprty specifies two or more fields (for a multiple-column list), set Bound Column to the number of the field that contains the value you want to store in the current table (1 is the first field, 2 is the second field, and so on).
• Limit To List (Data tab)—This is a combo box-only property. When the value is Yes, the users can only select values from the list; when the value is No, the users can enter new values.
• Column Count (Format tab)—The number of columns in the list box.
• Column Heads (Format tab)—If this property is Yes, the list columns are displayed with headers, whereby each header contains the name of the field.
• Column Widths (Format tab)—The width, in inches, of each column, separated by semicolons.
• List Rows (Format tab)—This is a combo box-only property, and it specifies the number of items the users see when they click the list.
• Multi Select (Other tab)—This is a list box-only property. If this property is None, users can select only one item at a time; if this value is Simple, users can select multiple items by clicking them; if this value is Extended, users must hold down the Ctrl key to select multiple items (or hold down Shift to select multiple items that appear consecutively in the list).