Home > Articles > Data > Access

This chapter is from the book

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).
    Figure 3.5

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

    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.

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.

To learn about inner joins, see "Establishing Table Relationships," p. 267. (Chapter 12)

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

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.
  • 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).

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020