Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jun 5, 2007

Designing Forms for Efficient and Accurate Data Entry - Page 2

By DatabaseJournal.com Staff

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.

Tip - Another way to ensure data accuracy is to set up a field with a default value that Access enters into the field automatically when the user starts a new record. This can be a literal value such as 0 for a numeric field, or a formula such as =Date() for a date/time field. In the control's Property Sheet, display the Data tab and type the value in the Default Value property.

Caution - 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 more than one occasion) which assumes that once you are happy with your form's layout, format, and data validation, then 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 only takes 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.

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.

Caution - 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 (I discuss it in more detail in Chapter 4, "Designing Forms for Business Use"), 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 situation, 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 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.

  1. 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.

  2. 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.

  1. 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.

  2. 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.

  3. 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.

Tip - If you already have an "unframed" option button on your form, you can still insert it into an option group. 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. 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.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM