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 2007 provides some great ways to show information from related tables, thus making data input much simpler.
In Chapter 10, “Using Forms,” 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 2007 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 technobabble 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 the 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 20 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 11–35, to help you out.
Figure 11–35: 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 on 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 Look Up The Values In A Table Or Query option, and then click the Next button 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 in 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 4. 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 11–36. 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 in Figure 11–36 that we show both the Data and the Format tabs so that you can see the properties we are discussing.
Figure 11–36: The Combo Box Wizard set these properties for the CategoryDescription field.
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 11–37. 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 fxmplProducts2 in the sample database.)
Figure 11–37: A combo box for the CategoryDescription field makes it much easier for the user to select a correct value.
|Inside Out-Having Access Select Closest Matches While Typing|| |
If you want Access to select the closest matching entry when you type a few leading characters in a combo box, set the control’s Auto Expand property to Yes.
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.
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 13.
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 11–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 onto the form in the open space you left on the form. Your form in Design view should now look like the one shown in Figure 11–38. 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 11–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.
Figure 11–38: The Products form now contains a check box control to display the TrialVersion field.
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 11–39. One of the interesting side effects of using a special control to display data in a form is that the control properties carry over to Datasheet view. Switch to the Datasheet view of this form. The CategoryDescription field is displayed as a dropdown list on the datasheet and the TrialVersion field still looks like a check box. You might decide to design some forms to be used in Datasheet view, but you can customize the look of the datasheet by using controls other than text boxes while in Design view. By the way, this design sample is saved as fxmplProducts3 in the sample database.
Figure 11–39: Your Products form now has both a combo box control and a check box control to simplify data entry.
By now, you should be getting a feel for the process of building forms. In the next chapter, you’ll learn how to customize the appearance of your forms.