| 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). For example, an invoice form might offer the users three choices for freight or four choices for credit cards. Again, rather than 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. Rather than 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 into a drop-down list.
This section 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. Avoid Form Complacency This is as good a place as any to warn you against what I call "form complacency." This is the attitude (which I've succumbed to myself on many an occasion) that assumes that after you are happy with your form's layout, format, and data validation, other people will automatically be happy with those things, too. Probably not! Other people will almost certainly approach the form differently, and they'll almost always have trouble figuring out how it works and what's expected of them. In other words, always "test drive" your form by letting other users take their best shots at it. It takes only a little extra time, and the suggested changes they come up with (and there will be suggestions, believe me) will save you time in the long run. Better yet, ask your future users in advance (that is, while you're contemplating the form's design) what features and layout they prefer. |
Working with Yes/No Fields 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's 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. |  Click the Check Box button in the Toolbox.
| 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 click View, Properties.
| 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.
|
Option Groups and Check Boxes Many form designers like to use an option group as a way of "framing" a number of related controls. This is often a good idea, but you need to be careful: If you add the option group and then insert the check boxes within the group, Access treats the check boxes as mutually exclusive options. That is, the users can activate only one check box at a time. To avoid this, add the check boxes to the frame first, and then draw the option group around them. |
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 either display 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. |  Click the Toggle button in the Toolbox.
| 2. | Draw the toggle button on the form.
| 3. | Click View, Properties to display the toggle button's property sheet.
| 4. | In the Format tab, you have two choices that determine what appears on the face of the button:
CaptionUse 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.)
PictureUse 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 4.29. Either use the Available Pictures list to click an image, or click Browse to choose an image from the Select Picture dialog box.
Figure 4.29. 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. Numeric Fields Only Option button values must be numeric. Therefore, you can use only option groups and option buttons 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. |  Make sure the Control Wizards button is activated and then click the Option Group button in the Toolbox.
| 2. | Draw the option group on the form. Access launches the Option Group Wizard.
| 3. | For each option button you want, enter 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 is activated automatically when the users start 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 4.30. Note that each value must be unique. Click Next when you're done.
Figure 4.30. 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 UseClick this option to have Access save the option group value.
Store the Value in This FieldClick 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 frameuse the field name or something similar) and then click Finish to complete the wizard.
|
Handling an "Unframed" Option Button If you already have an "unframed" option button on your form, you can still insert it into an option group. Just select the button, cut it to the Clipboard, select the option group (by clicking its frame), and paste. Access adds the button to the option group. |
Creating an Option Group by Hand If you'd rather create the option group yourself, here are the steps to follow: 1. |  Make sure the Control Wizards button is deactivated and then click the Option Group button in the Toolbox.
| 2. | Draw the option group on the form.
| 3. | Click Option Button in the Toolbox.
| 4. | Draw the option button inside the option group.
| 5. | Click View, Properties 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 would be named Label11.)
| 8. | In the Format tab, use the Caption property to specify text that appears alongside the option button.
| 9. | Repeat steps 38 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.
|
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 there might be instances where you 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 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) to enter a different value using the associated text box.
Consider the Size of the List Control Another consideration you need to bear in mind when deciding between a list box and a combo box is the size of each control on the form. A list box is usually large enough to show at least three or four items in the list, whereas a combo box always shows only a single item (the users clicks the list to choose another). Therefore, the list box always takes up quite a bit more room than the combo box, so keep that in mind when designing your form. If you don't have much room, but you don't want the users to be able to add different values to the field, you'll see later that it's possible to restrict the combo box to only the values in the list. |
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 gets 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 ProductID fields. If you're putting together a form based on the Order Details table and you add the ProductID field, Access creates a combo box list and populates it with the values from the Products table's ProductName field. (Why ProductName and not ProductID? Because in the design for the Order Details table, the ProductID field's Row Source property [in the Lookup tab] specifies an SQL statement that selects the ProductName field from the Products table.) 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. | Make sure the Control Wizards button is activated in the Toolbox.
| 2. | Click one of the following controls in the Toolbox:
 Combo Box
 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 CustomerName 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, select the I Want the List Box to Look Up the Values in a Table or Query option and then click Next.
| 2. | Select 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 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, select 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 ContactTitle field and use the unique values in that to populate the list. (This is a good example of 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.) 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, select 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 At times, displaying a single column of values in a list isn't enough. For example, if you're working with data from the Northwind Employees table, displaying only the ProductName 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 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. | 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 to the 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 4.31 shows a form that uses a two-column combo box to display both the ProductName field and the CategoryName field from an inner join query of the Products and Categories tables. Figure 4.31. By using an inner join query of the Products and Categories tables, this combo box displays both the ProductName field and the corresponding CategoryName field. 
Using Text Boxes as Calculated Form Controls In a query, you can use an expression to build a calculated column that, when you run the query, displays the result of the expression for each record in the query dynaset. You can do something similar in your forms by setting up a text box to display the results of an expression. This expression can use any of the Access operators, operands, and functions, and it can use the values in both bound and unbound controls. Here are the steps to follow to create a calculated text box control: 1. |  Click the Text Box button in the Toolbox.
| 2. | Draw the text box on the form. Access adds the text box and an associated label.
| 3. | Click the text box to select it.
| 4. | Choose View, Properties (or press Alt+Enter) to display the control's property sheet.
| 5. | Choose the Data tab.
| 6. | Enter the expression in the Control Source property.
| 7. | Close the property sheet.
|
For example, Figure 4.32 shows the property sheet for a text box that has the expression =Date() as its Control Source property. In the Form view in Figure 4.33, you can see that the text box displays the current date. (Note that I entered the format string mmmm d, yyyy in the text box's Format property to get the date format shown in Figure 4.33.) Figure 4.32. To create a calculated control, enter an expression into a text box's Control Source property. 
Figure 4.33. In the Form view, the text box displays the current date. 
Calculated Text Cannot Be Edited Users can't edit the text in a calculated text box. They can move the insertion point cursor within the control and then can select and copy the control text, but if they try to edit the text, Access beeps the speaker and displays the following in the status bar (where expression is the expression in the Control Source property): Control can't be edited; it's bound to the expression 'expression'.
|
|
