One drawback to working with a relational database is that often you have to deal with information stored in multiple tables. That’s not a problem if you’re using a query to link data, but working with multiple tables can be confusing if you’re entering new data. Access 2010 provides some great ways to show information from related tables, thus making data input much simpler.
In Chapter 12, you saw how you can use a combo box or a list box to present a list of potential values for a control. To create the list, you can type the values in the Row Source property box of the control. You can also specify a table or a query as the source of the values in the list. Access 2010 displays the currently selected value in the text box portion of the combo box or as a highlighted selection in the list.
The CategoryDescription field in the tblProducts table is a simple Text data type. To help ensure data consistency, there’s a separate lookup table that contains a list of predefined product types. There’s also a referential-integrity rule that keeps you from entering anything other than a predefined type in the CategoryDescription field. However, you can type anything you like in the CategoryDescription text box (labeled Product Type) that the Form Wizard designed. Go ahead and type any random string of characters in the text box and then try to save the record. You should see an unfriendly message about “related record is required in ‘tlkpProductCategory’.”
You can help avoid this problem by providing a combo box to edit and display the CategoryDescription field instead. The combo box can display the list of valid values from tlkpProductCategory lookup table to make it easy for your user to choose a valid value. The combo box can also limit what the user enters to only values in the list. In Chapter 25, you’ll learn how to write Visual Basic code to detect when a user tries to enter something that’s not in the list so that you can provide your own, more user-friendly, message.
To see how a combo box works, you can replace the CategoryDescription text box control with a combo box on the Products form. In Design view, select the CategoryDescription text box control and then press the Delete key to remove the text box control from the form (this also removes the related label control). Be sure the Use Control Wizards button is selected in the Controls group on the Design tab. Display the field list by clicking the Add Existing Fields button in the Tools group on the same Design tab. Next, click the Combo Box button in the Controls group and drag the CategoryDescription field from the field list to the form. The new control appears on the form, and Access starts the Combo Box Wizard, as shown in Figure 13-34, to help you out.
Figure 13-34. After you drop the CategoryDescription field onto the form grid, Access opens the first page of the Combo Box Wizard.
Inside Out: Manually Changing a Text Box to a Combo Box
You can change a text box to a combo box by right-clicking the text box control, clicking Change To on the shortcut menu, and then clicking Combo Box on the submenu. However, after you change a text box to a combo box in this way, you have to set the properties for the display list yourself.
Follow this procedure to build your combo box:
You want the combo box to display values from the tlkpProductCategory lookup table, so select the I Want The Combo Box To Get The Values From Another Table Or Query option, and then click Next to go to the next page.
On the second page, the wizard displays a list of available tables in the database. Note that the wizard also provides an option to view queries or both tables and queries. Scroll down the list and click Table: tlkpProductCategory to select that table, and click Next to go to the next page.
On the third page, the wizard shows you the single field in the table, CategoryDescription. Select that field and click the right arrow (>) to move it to the Selected Fields list. Click Next to go on.
The fourth page allows you to select up to four fields to sort either Ascending or Descending. Click the arrow to the right of the first field and then select the CategoryDescription field. The button next to the first box indicates Ascending, and you want to leave it that way. If you click the button, it changes to Descending, which is not what you want. (You can click the button again to set it back.) Click Next to go to the next page.
The wizard shows you the lookup values that your combo box will display as an embedded datasheet, as shown here. To size a column, click on the dividing line at the right edge of a column at the top, and drag the line. You can adjust the size of the column to be sure it displays all the available descriptions properly. Click Next to go on.
On the next page, the wizard asks whether you want to store the value from the combo box in a field from the table or query that you’re updating with this form or simply save the value selected in an unbound control “for later use.” You’ll see in Part VI and 9 of this book that unbound controls are useful for storing calculated values or for providing a way for the user to enter parameter data for use by your macros or Visual Basic procedures. In this case, you want to update the CategoryDescription field, so be sure to select the Store That Value In This Field option and verify that CategoryDescription is selected in the list. Click Next to go to the last page of the wizard.
On the final page, shown here, the wizard suggests a caption that you probably want to correct. In this case, enter Product Type in the box. Click Finish, and you’re all done.
If you have the property sheet open, you can study the properties set by the Combo Box Wizard, as shown in Figure 13-35. The Control Source property shows that the combo box is bound to the CategoryDescription field. The Row Source Type property indicates that the data filling the combo box comes from the table or query entered in the Row Source property box. Notice that the wizard generated an SQL statement in the Row Source property box. You can also specify a value list as the Row Source property, or you can ask Access to create a list from the names of fields in the query or table specified in the Row Source property. Please note that in Figure 13-35, we show both the Data and the Format tabs so that you can see the properties we are discussing.
The Column Count property is set to 1 to indicate that one column should be created from the list. You have the option of asking Access to display column headings when the combo box is open, but you don’t need that for this example, so leave the Column Heads property set to No. The wizard sets the Column Widths property based on the width you set in step 5. The next property on the Data tab, Bound Column, indicates that the first column (the only column in this case) is the one that sets the value of the combo box and therefore the value of the bound field in the table.
When you open the form in Form view, it should look like the one shown in Figure 13-36. You can see that the CategoryDescription combo box now shows the list of valid values from the lookup table. Notice also that the label the wizard attached looks more like the labels that the Form Wizard originally created. You can make this label look like the others by changing it to a bold font and right-aligning it. (You can find this form saved as frmXmplProducts2 in the sample database.)
If your table contains a field that has a yes/no, a true/false, or an on/off value, you can choose from three types of controls that graphically display and set the status of this type of field: toggle buttons, check boxes, and option buttons.
Inside Out: Choosing Toggle Buttons and Check Boxes and Option Buttons—How to Decide?
Although you can certainly use any of these three controls to display an underlying Yes/No data type, you should try to use these controls in your application similarly to the way Windows uses them. Your users might be confused if you try to use them in a different way. Here are some good principles to follow:
Use a toggle button to display an option value. A toggle button works best to display an option that is on or off.
Use a check box to display all simple yes/no or true/false values.
Use an option button when the user needs to make a choice from several options. You should not use an option button to display simple yes/no or true/false values.
We personally never use a toggle button or an option button except inside an option group control. You can learn more about working with the option group control in Chapter 15.
The tblProducts table has a TrialVersion field that indicates whether the particular product is a free trial edition that expires in a specific number of days. As you can see in the original text box control created by the Form Wizard (see Figure 13-29), the word Yes or No appears, depending on the value in the underlying field. This field might be more appealing and understandable if it were displayed in a check box control.
To change the TrialVersion control on the Products form, first delete the TrialVersion text box control. Display the field list by clicking the Add Existing Fields button in the Tools group. Next, click the Check Box button in the Controls group, and then drag the TrialVersion field from the field list into the open space you left on the form. Your form in Design view should now look like the one shown in Figure 13-37. Notice that the default check box also includes a label, but the label is positioned to the right of the control and does not include a colon. If you want to move the label, select it, and then use the large handle, shown earlier in Figure 13-15, to move the label to the left of the check box. You should also change the font to bold to match the other labels.
After making final adjustments to the TrialVersion label, click the arrow under the Views button and click Form View to see the result. Your form should look like the one shown in Figure 13-38. This design sample is saved as frmXmplProducts3 in the sample database.
