| With some background now underpinning the work, let's now focus on building the objects for more robust and effective forms. Text Boxes and Accompanying Labels Text boxes are the key control in forms. By now you're generally familiar with how they work; Chapters 6 and 10 can provide refreshers. There are some wrinkles to adding and positioning text boxes and their accompanying labels. A common mistake is to forget to leave space for the label. Try this exercise: 1. | Select the CustFax field at the end of the field list.
| 2. | Drag it to the Detail area and drop it 0.5 inch from the left and two rows of dots below the State field (see Figure 11.6). A text box and its accompanying label are created.
Figure 11.6. The CustFax field has been dropped. 
| 3. | Click View to see the form. The Fax Number controls are pushed too far to the left.
| 4. | You could reposition the controls using a variety of techniques. But it's sometimes easier to have another go at drag-and-drop first.
| 5. | Click View to return to Design view. The CustFax controls should already be selected. Press Delete.
| 6. | From the field list, select CustFax. Drag and drop it so the left edge of the pointer is directly below the left border of the CustState text box (see Figure 11.7).
Figure 11.7. The CustFax text box is about to be dropped. 
|
NOTE You can choose the Edit, Undo command to undo a single design action. You can also undo (or redo) a series of design actions by clicking the arrow on the Undo (or Redo) button (refer to Figure 11.3) and selecting the actions you want undone (or redone).
The Fax Number controls should now be about in the right spot, but the label, text box, or both might be slightly misaligned. Here are two alternatives: If you want to move both text box and label as a couple, select either and move the pointer to the top border of the selected control. When the pointer becomes an open hand, drag in any direction (see Figure 11.8). Figure 11.8. Use the open-hand pointer to move both text box and label. Note that you want to position the pointer away from the upper-left move handle. 
If you want to move one control without the other, select the control you want to reposition and place the pointer at the move handle in the upper-left corner. When the pointer becomes a pointing finger, drag the solitary control where you want it (see Figure 11.9). Figure 11.9. Use the pointing-finger pointer to move either text box or label alone. In this case, you want the pointer directly on the move handle. 
TIP Frequently you will want to add just a text box and dispense with the accompanying label. If you're adding just one or two boxes, it's easier to create the text box/label pair, select the unwanted label, and delete it. But if you're adding many fields, you'll want to change the default to create text boxes alone. In the toolbox, select the Text Box tool and choose View, Properties, Format tab. Edit the Auto Label setting in the Default Text Box property sheet to No.
Labels You can create standalone labels for form titles, form instructions, and other text you want on your form, as follows: Select the label tool in the toolbox and drag the pointer to the appropriate section. With the crosshairs as your guide, click where you want the label and type your text in the box. Press Enter when you're finished.
You worked with labels often in the previous chapter on reports. They are a fairly easy topic, so I won't belabor them here. Combo Boxes The best way to introduce combo boxes, an extremely useful form tool, is to see one in action: 1. | Click View to go to Form view.
| 2. | Choose Data Entry on the Records menu.
| 3. | Click the drop-down arrow in the Last Name field. Scroll down and choose Isaacs, Gloria from the list (see Figure 11.10).
Figure 11.10. A combo box with several fields. 
NOTE When you select a name from the combo box, the other customer fieldsFirst Name, Street Address, and so onare automatically filled in. The existence of a combo box isn't the cause of this action, although it is built on the same principle of a one-to-many relationship between two tables with a field of matching data. You can learn more about this subject by reading the Access Help article "About AutoLookup Queries That Automatically Fill in Data (MDB)." | 4. | Press Escape and choose Records, Remove Filter/Sort to exit Data Entry mode.
| 5. | Click View to return to Design view. Right-click the CustomerID control (it's to the right of Last Name) and choose Properties. As you can see from the title, this control is a combo box.
A combo box stores the CustomerID field, which is the primary key of the Customers table and a foreign key in the Orders table. Access "looks up" the value of the LastName field in the Customers table and displays it. The other fields in the combo box columns that you saw in the drop-down list are there to help you select the correct record and are optional.
|
Now you'll re-create that same combo box: 1. | Close the property sheet. With the CustomerID control selected, press Delete to delete the combo box and its label.
| 2. | In the toolbox, make sure the Control Wizards button (see Figure 11.11) is selected. (If you don't see the toolbox, choose View, Toolbox.)
Figure 11.11. The toolbox can be displayed and hidden by choosing View, Toolbox. 
| 3. | Click the Combo Box tool and drag the crosshair so that it's one row from the top and a little less than 1 inch from the left.
Don't worry if you don't get the position just right. You'll fiddle with the dimensions later so the combo box is in the same place it was previously.
| 4. | In the first dialog box, choose the first selection, I Want the Combo Box to Look Up the Values in a Table or Query. Click Next.
| 5. | Choose tblCustomers and click Next. Double-click the CustLastName field to move it from the Available Fields column to the Selected Fields column. Do the same for the CustFirstName, CustStreetAddress, CustCity, CustState, CustZIP, and CustPhone fields.
The actual values you want to display are customer last names, which are in the first column. But to make sure you choose the correct last name, you want to have the other fields in view for verification.
| 6. | Click Next. In this dialog box, you can sort by up to four fields. Open the first drop-down box and choose CustLastName. Open the second drop-down box and choose CustFirstName. You can keep the sorts at Ascending; for descending sorts, you would just click the button. Click Next.
NOTE Sorting in the Combo Box Wizard is a new feature in Access 2003. If you're using 2002 or earlier versions, I address this shortly. | 7. | Leave the column widths as is. The Hide Key Column check box should be checked. (There's no reason why you need to see the actual CustomerID.) Click Next.
| 8. | Click Store the Value in This Field, open the drop-down list, and select CustomerID. Click Next.
| 9. | Edit the label to Last Name. Click Finish.
The next two steps align the new combo box controls. One way to do that is to open the property sheet for the control you want to align to, find the setting for the relevant property (Left, Width, and so on), and enter the same setting for the control that needs to be aligned.
The text box below CustomerID is CustFirstName. The Left property for the CustFirstName text box is 0.7917". The control to the right of CustomerID is the ZIP Code label. Its Top property is 0.833". You will enter these settings in the property sheet.
| 10. | With the CustomerID control selected, choose View, Properties, Format tab. Edit the Left property to 0.7917" ; edit the Top to 0.0833". (If Access changes these numbers by a few thousandths, don't worry.)
| 11. | Click the Last Name label. Edit the Left property to 0.0417". Close the property sheet. Choose File, Save to save your changes.
| 12. | Click View and open the drop-down box to see your completed combo box. All the field columns are there as you entered them.
|
If you're using Access 2003, you're set. If you're using any earlier version, you'll need to sort the columns by name. Even if you're using 2003, I suggest that you look at the following steps. These will help you understand the underlying source of a combo box and enable you to add or adjust sorts as you like. 1. | Click View to return to Design view. Right-click the CustomerID combo box and choose Properties.
| 2. | Click the Data tab, click in the Row Source, and click the ellipsis button at the end of the row.
The SQL statement window opens (see Figure 11.12). If you are using Access 2003, you will se ascending sorts in the CustLastName and CustFirstName columns. If you are using earlier versions, it's easy to add these sorts.
Figure 11.12. The SQL statement for the combo box. 
| 3. | In the Sort row of the LastName column, type a for an ascending sort. Do the same in the FirstName column. Close the window and confirm your changes.
|
NOTE In this example, you want to choose only customers that are in the combo box list. In other instances, however, you might want to set your combo box so that you can enter values that are not on the list by typing them directly into the box. In that case, you need to set Limit to List on the Data tab of the property sheet to No.
List Boxes A relative of the combo box is the list box. This control displays all available values for the field at all times. The user clicks the value for that specific record; it becomes highlighted, distinguishing it from the other values. A different value can be selected by simply clicking it. Let's create a list box for ShipID: 1. | Click View to switch to Form view.
| 2. | Click the down arrow in the Shipper field.
As you can see, it's a combo box, albeit simpler than the one you just used. With only four values to choose from, it's a good candidate for a list box.
| 3. | Click the drop-down arrow again to close the box. Click View to switch to Design view.
Because you'll need some more room for a list box, you'll move the ShipCost controls and the Overnight option group (option groups are discussed later).
| 4. | Drag the ShipCost text box and its accompanying label so that the text box is directly below the ShippedDate text box. Leave two or three rows of dots between controls. (You will fix the positioning of controls at the end of the chapter.)
| 5. | Select the subform. Press Ctrl+Down Arrow several times until the subform is 2 inches from the top (about 0.5 inch below the ShipCost controls).
Of course, there are other ways to position this control. The technique you just employed is "nudging," most often used to move controls a wee bit.
| 6. | Click directly on Overnight. Drag the entire option group down to the 1.25-inch mark on the vertical ruler. Your form should look something like Figure 11.13.
Figure 11.13. You can move controls to make additional room for list boxes and other tools. 
| 7. | Right-click the ShipID control. Choose Change To, List Box. Choose File, Save to save your changes.
| 8. | Click View to switch to Form view.
| 9. | The control now shows all fields, and the shipper for that specific record is highlighted (see Figure 11.14). Scroll through a few records to get a feel for how the list box works.
Figure 11.14. The Orders window has been resized to show the Shipper list box for the first record. All possible values are displayed, and the Faster Delivery value for the current record is highlighted. 
|
To create a list box from scratch, you would use the List Box tool in the toolbox (be sure the Control Wizard button is selected; refer to Figure 11.11). It works nearly the same as the combo box, and if you understand that tool, you shouldn't have trouble using it. List boxes do take up space that can be saved with a text box or combo box, so it's usually best to use them when there are only a few values. NOTE You don't have to use the control wizards to create combo boxes, list boxes, and other form tools. But usually they are the fastest, most convenient way to create these controls.
Option Groups Another way to display values when there are just a few values to choose from is the option group. The Overnight control in the Orders form is an example (see Figure 11.15). The option group lets you use radio buttons, check boxes, or toggle buttons to designate the correct value. Figure 11.15. An option group is used for the Overnight field to display its values. 
Option groups are surprisingly complex creatures. They have several components, and it's not always easy to figure out which property sheet you should edit to make a desired change. They also require a little thinking to create. But you can see the advantages of using them: The Overnight title fits nicely inside the overall control, and the radio button (or check box, or toggle button) is a neat visual device. You can turn the ShipID field into an option group. There's no Change To menu item for an option group, unfortunately, so you'll create it from scratch (with a wizard's help, of course): 1. | Click View to return to Design view. Select the ShipID list box and press Delete.
| 2. | The Options Wizard button in the toolbox should be selected.
Click the Option Group control in the toolbox (refer to Figure 11.11 if you need help locating it). Drag the crosshair to the Design section and click 4 inches from the left and about two rows of dots from the top. The Option Group Wizard opens.
| 3. | In the first dialog box, you enter the names of the shippers. Put them in ascending (alphabetical) order. Type Apollo Shipping and press the down arrow (do not press Enter). Type EAN Lines and press the down arrow. Type Faster Delivery and press the down arrow. Type USPS and click Next.
| 4. | Click No, I Don't Want a Default. Click Next.
Access has assigned values for each name. The number in the value column must match the primary key for each shipper in the label names column. Because you alphabetized the shipper names in the wizard dialog box, the labels EAN Lines and Faster Delivery are no longer in sync with the primary key (the ShipID) and need to be changed.
| 5. | Edit the value for EAN Lines to 3, and for Faster Delivery to 2 (see Figure 11.16). Click Next.
Figure 11.16. The values for EAN Lines and Faster Delivery have been edited so that ShipID's and shipper names match. 
| 6. | Click Store the Value in This Field. Open the drop-down list and select ShipID. Click Next.
| 7. | In this dialog box, you choose the type of controls you want and the style. As you switch selections, the Sample in the dialog box changes to reflect your choices. Choose Option Buttons and Sunken style. Click Next.
| 8. | Edit the caption name to Shipper and click Finish.
| 9. | Click View to see the option group (see Figure 11.17).
Figure 11.17. The option group in Form view. 
The shipper option group looks fine, but it's out of sync with the Overnight option group below it (Figure 11.19 shows the two groups in design view). You'll fix it at the end of the chapter.
Figure 11.19. The Object Selector displays the currently selected form element. 
| 10. | Click View to return to Design view.
|
The option group (Figure 11.18) contains the following elements: Frame Actually, the frame is not a separate element; it is the option group. If you select the frame and open the property sheet, its title is Option Group, not Frame. If you want to delete the entire option group, you click the frame and press Delete. Option buttons These might seem to be only decorative elements (the same goes for check buttons or toggle buttons, if you had chosen those instead). But they actually contain the values for each record. Right-click any option button and choose Properties, Data tab. It has the option value (the ShipID) that you set in the wizard for that shipper. One problem you might have with option groups is a mix-up in option values. For example, if you hadn't edited the option values in the wizard, the EAN Lines button would mistakenly be selected whenever the order was delivered by Faster Deliveryand vice versa. You would need to edit this property for both option buttons to make sure it matches the ShipID (the primary key) in the underlying table. Primary key IDs and values in an alphabetized (or otherwise sorted) field often are not synchronized, so be on the lookout for mistakes in option values. Value labels These are the shipper names Apollo Shipping, EAN Lines, and so on. These labels hold no data; they merely describe the option buttons. Option group label This is the title of the option group, not the option group itself. If you delete this label, the rest of the option group still exists.
Figure 11.18. The option group in Design view. 
One final note about option groups might or might not be obvious: If you add a record (such as an additional shipper) to the underlying table, the option group in the form remains unchanged. To include the new shipper, your best bet is to delete the existing option group and use the wizard to build a new one. Subforms Compared with option groups, adding a subform to your form is relatively straightforward. There is one wrinkle: The main form must have a field that is on the "one" side of a one-to-many relationship, and the subform must have a field that's on the "many" side of the same relationship. 1. | Choose Tools, Relationships to open the Relationships window.
As you can see, the OrderID field in tblOrders is on the "one" side; the OrderID in tblOrderDetails is on the "many" side. Because the main form has the OrderID field from the Orders table, you shouldn't have any problem re-creating the Orders Details table as the subform.
| 2. | Close the Relationships window. In Design view, click the border of the subform control to select it.
Make sure you select the entire subform. As shown in Figure 11.19, the Object Selector should be tblOrderDetails.
| 3. | Press Delete to delete the subform.
| 4. | At this point, only two Access windows should be open: the Database window and the Orders form. Choose Window, Tile Vertically.
| 5. | In the Database window, click the Tables button and select the Order Details table.
| 6. | Drag it to the Detail section of the Orders Practice form. Drop it 2 inches from the top and 1 inch from the left (see Figure 11.20).
Figure 11.20. Drag the Order Details table from the Database window to the form. (It makes no difference whether the position of the two windows is switched.) 
| 7. | The Subform Wizard opens. The Choose From a List button should be selected, and Show tblOrderDetails for Each Record in qryOrders Using OrderID should be highlighted. Click Next.
| 8. | Keep the default title and click Finish.
| 9. | Minimize the Database window and maximize the form. Click View in the form to switch to Form view. You have the same subform you did before. Scroll through a few records to make sure that it works properly.
| 10. | A couple problems exist with the subform. Not all the fields are displayed fully. A smaller problem is that there is a label tblOrderDetails subform, which is superfluous. You'll take care of both problems at the end of the chapter.
| 11. | Click View to return to Design view. With the subform selected, press F4 to open the property sheet and click the Data tab.
| 12. | Notice that the Source Object is the tblOrderDetails subform. Also notice that the Link Child Fields and Link Master Fields properties are set to OrderID, the field with matching data in the two tables. The Child Fields link is the linking field in the subform; the Master Fields link is the field in the main form. Each time you move to a new record in the main form (which has records from the table on the "one" side of the relationship), the subform (which has records on the "many" side of the relationship) is revised to display the related records.
| 13. | Close the property sheet. Save your changes and close the form. In the Database window, click the Forms button. Select the tblOrderDetails subform and open it.
| 14. | This is the form that Access created when the subform was added. Note that it is a separate form in the Database window, and it can be used as a form on its own. If you edit the form, however, those changes will be reflected in the subform of the Orders Practice form.
| 15. | Close the tblOrderDetails subform.
|
TIP Another way to add a subform is to use the Subform/Subreport tool in the toolbox. Click the tool, drag it to the Design section, and position the crosshairs where you want the upper-left corner of the subform.
Open frmOrdersPractice from the NiftyLionsEndChap11.mdb database that you can download. Your form should resemble it. You'll do some work on the form at the end of the chapter to fix it up a bit. |
