The Conrad Systems Contacts sample database is full of interesting examples of forms. The rest of this chapter takes you on a tour of some of the major features of those forms and shows you some of the basic techniques for editing data in a form. In the next chapter, you’ll learn how to design and build forms for this database.
Begin by opening the Conrad Systems Contacts database (Contacts.accdb) and set the Navigation Pane to display only the forms. To do so, click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Forms under Filter By Group to see the list of available forms. Note that when you open the database, you see a copyright notice followed by a message telling you which form to open to start the application.
You’ll normally place the information that you want to display from the underlying table or query in the detail section in the center of the Form window. You can add a header at the top of the window or a footer at the bottom of the window to display information or controls that don’t need to change as you move through the records.
An interesting form in the Conrad Systems Contacts database that includes both a header and a footer is frmContactSummary. The application uses this form to display the summary results of a contact search whenever the search finds more than five matching contacts. You can also open this form directly from the Navigation Pane-if you do so, it will show you all the contacts in the database. Find the frmContactSummary form in the forms list in the Navigation Pane, right-click the form name, and then click the Open command on the shortcut menu to see a window similar to the one shown in Figure 10–1.
Figure 10–1: The frmContactSummary form has a header, a detail section, and a footer.
The area at the top of the window containing the title Contact Search Summary is the header for the form. The header also includes the column names. The area at the bottom of the window is the footer for the form. You can click the View Details button to see all details for the currently selected contact (the contact with the arrow on the row selector), or you can click Close to close the form. In the lower-left corner of the form is the Record Number box that you saw in tables and queries in Datasheet view. Click the arrow button immediately to the right of the record number to move the row selector arrow to the next contact record in the detail section of the form; notice that the header and footer don’t change when you do this. If you move down several records, you can see the records scroll up in the detail section of the form.
If you click the View Details button in the footer, this form closes and the frmContacts form opens, showing details of the contact record that you selected before you clicked the button. The way the form is designed, the View Details button opens the frmContacts form using a filter to show you the currently selected contact. If you decide that you don’t want to see details, you can click the Close button in the form footer to dismiss the form.
When you have a lot of information from each record to display in a form, you can design a multiple-page form. Open the frmContactsPages form in the Conrad Systems Contacts database to see an example. When you open the form, you’ll see the first page of contact data for the first contact. You can use the Record Number box and the buttons in the lower-left corner of the form to move through the records, viewing the first page of information for each contact. Figure 10–2 shows the first page of the second contact record-the records are sorted by last name. (For those of you who want to visit Jeff’s Web site, that’s his real Web site address in the form!) To see the second page of information for any contact, press the Page Down key. Figure 10–3 shows the second page of Jeff’s contact record. (Notice that this form has a header but no footer.) As you view different pages of a multiple-page form, the header at the top of the form (with the form title) doesn’t change.
Figure 10–2: This is the first page of a record in the multiple-page frmContactsPages form.
Figure 10–3: Here is the second page of the same record shown in Figure 10–2.
You can create another type of form that is useful for browsing through and editing a list of records when each record has only a few data fields. This type of form is called a continuous form. Rather than showing you only a single record at a time, a continuous form displays formatted records one after the other, in the manner of a datasheet.
The frmContactSummary form shown earlier in Figure 10–1 is a simple continuous form. The frmLkpContactEventTypes form, shown in Figure 10–4, is also a continuous form. You can use the vertical scroll bar to move through the record display, or you can click the buttons in the lower-left corner of the form to move from record to record. Also, you can click the New Record button to move to the blank row below the last record. The application uses this form to let you view and edit the different types of contact events that you might want to log.
Figure 10–4: The frmLkpContactEventTypes form in the Conrad Systems Contacts database is a continuous form.
A new type of form view in Access 2007 called Split Form allows you to simultaneously display a record in a regular form view and see a list of records in Datasheet view. Open the frmProducts form in the Conrad Systems Contacts database to see an example, as shown in Figure 10–5. When you open the form, you’ll see that the upper half of the form displays the details about one specific product and the lower half of the form displays the complete list of 11 products offered by Conrad Systems.
You can use the Record Number box and the buttons in the lower-left corner of the form to move through the records. Click the Next Record button and notice that the record you are currently viewing in the top half becomes the highlighted record in the bottom half of the form. Depending on the settings in the form’s design, you can edit the information about any specific product in either the top or the bottom half of the form.
Figure 10–5: The frmProducts form in the Conrad Systems Contacts database is a split form.
A subform is a good way to show related data from the many side of a one-to-many relationship. For example, the frmCompanies form, shown in Figure 10–6, has a subform to display the related contacts. Although this form looks much like a single display panel, it has a subform (which looks more like a datasheet than a form) embedded in the main form. The main part of the frmCompanies form displays information from the tblCompanies table, while the subform in the lower part of the window shows information from the tblCompanyContacts table about the contacts related to the current company.
Figure 10–6: The frmCompanies form has an embedded subform that shows the related contacts.
This form looks quite complicated, but it really isn’t difficult to build. Because the Conrad Systems Contacts database is well designed, it doesn’t take much effort to build the queries that allow the form to display information from three different tables. Most of the work of creating the form goes into selecting and placing the controls that display the data. To link a subform to a main form, you have to set only two properties that tell Access which linking fields to use. (These are actually the same Link Master Fields and Link Child Fields properties you learned about in Chapter 8, “Building Complex Queries,” when you defined a subdatasheet for a query.) In Chapter 13, “Advanced Form Design,” you’ll build a subform and link it to a form.
Sometimes it’s useful to provide information in a window that stays on top regardless of where you move the focus in your application. You’ve probably noticed that the default behavior for windows in Microsoft Windows is for the active window to move to the front and for all other windows to move behind the active one. One exception in Access is the property sheet for any object in Design view. If you grab the property sheet and undock it, it stays floating on top so that you can still access its settings regardless of what you are doing behind it. This sort of floating window is called a pop-up window.
You can create forms in Access that open in pop-up windows (called pop-up forms in Access). If you open any form in the Conrad Systems Contacts application and then click the About command on the custom Ribbon, this opens the frmAbout form shown in Figure 10–7, which is designed as a pop-up form. See Chapter 24, “The Finishing Touches,” for more details about how to create custom Ribbons for forms. If you still have frmCompanies open, you can click the About command on the Ribbon. Or, you can switch to the Navigation Pane and open the frmAbout form directly to see how it behaves. Notice that if you click in the open form or the window behind it, the frmAbout form stays on top. Click the Close button on the pop-up form to close it.
Figure 10–7: The frmAbout pop-up form “floats” on top of frmCompanies, which has the focus.
As you add functionality to your application, you’ll encounter situations in which you need to obtain some input from the user or convey some important information to the user before Access can proceed. Access 2007 provides a special type of form, called a modal form, which requires a response before the user can continue working in the application. The fdlgContactSearch dialog box in the Conrad Systems Contacts database, shown in Figure 10–8, is a modal form. This dialog box normally opens when you click the Contacts button on the main switchboard form and then click the Search button on the resulting Select Contacts (frmContactList) form. This dialog box also opens if you first open the frmContactsPlain form and then click the Search button. You can open the form on which the dialog box is based directly from the Navigation Pane. You’ll notice that as long as this dialog box is open, you can’t select any other window or Ribbon command in the application. To proceed, you must either enter some search criteria and click the Search button or click the Cancel button to dismiss the form.
Figure 10–8: The fdlgContactSearch form in the Conrad Systems Contacts database is a modal form that opens as a Windows dialog box.
|Inside Out-Using Name Prefixes to Organize Your Objects|| |
Have you noticed the different prefixes on the form names that we designed in the Conrad Systems Contacts application? We like to create a prefix that helps us know more about the type of form when we look at the form list in the Navigation Pane. For example, we prefix the names of forms that are designed to open in PivotChart view with cht. We prefix dialog forms with fdlg, normal edit forms with frm, forms designed to edit lookup tables with frmLkp, and subforms with fsub. You may want to adopt a similar naming convention to help you keep your list of forms organized.
The information in a form is contained in controls. The most common control you’ll use on a form is a simple text box. A text box can display data from an underlying table or query, or it can display the result of an expression calculated in the control. You’ve probably noticed that many controls allow you to choose from among several values or to see additional content. You can also use controls to trigger a macro or a Visual Basic procedure. These controls are discussed in the next five sections.
Whenever the data you’re displaying can have only two or three valid values, you can use option buttons, check boxes, or toggle buttons to see or set the value you want in the field. For example, when there are two values, as in the case of a simple Yes/No field, you can use a check box to graphically display the value in the field. A check box that’s selected means the value is Yes, and a check box that’s cleared means the value is No. The Inactive control on the frmContactsPages form (see Figure 10–2) and the Default? control in the subform of frmCompanies (see Figure 10–6) are good examples of the use of a check box.
Stand-alone option buttons and toggle buttons work in the same way as a check box. When the value of an option button is Yes or True, the option button has a black dot in it. When the value of an option button is No or False, the option button appears empty. Likewise a toggle button appears pressed in when True, and not pressed in when False.
To provide a graphical choice among more than two values, you can place option buttons, check boxes, or toggle buttons in an option group. When grouped this way, each control in the group should have a unique integer value. When the control appears selected, the value of the option group is the value of the control. Because an option group can have only one value, when you select a control within the group, all other controls in the group appear unselected because their values no longer match the value of the option group.
For example, open frmProducts (this form displays the different products available), and click the Print button to see the fdlgProductPrintOptions form (shown in Figure 10–9) that lists the various contact reporting options. (You cannot open fdlgProductPrintOptions directly from the Navigation Pane-it has Visual Basic code that runs when the form opens to verify that the companion frmProducts form is already open. If not, the code tells Access to not allow the form to open.) If you open this form and click the available option buttons, you can see that when you click one button, the previously selected one clears. When you click one of the sales report buttons on this form, the form reveals additional date range options for your sales report.
Figure 10–9: You can see option groups on the fdlgProductPrintOptions form.
When you want to display a list of data values in an open list, a list box is a good choice. When you view objects in Windows Explorer, the list of file names and properties in the pane on the right side when you’re in Details view is a list box.
A list box can show a list of values you entered when you designed the control, a list of values returned by an SQL statement, the values of one or more fields in a table or in a query, or a list of field names from a table or a query. When you select a value from the list, you set the value of the control. You can use a list box on a form that edits data to display the value of one of the fields. When you choose a new value in the list box, you update the underlying field.
You can also define a list box in which you can select multiple values. When you do this, however, the list box cannot update an underlying field. This type of list box is useful to allow a user to choose multiple items or options that your application code will use to perform some action. As we discussed in “Working with Multi-Value Lookup Fields” on page 245, Multi-Value Field Lookups, when bound to a combo box, do provide a list box that allows you to select and save multiple values back to the table.
In the example shown in Figure 10–10 (the frmContactList form), the list box allows multiple selections and includes the set of names from the tblContacts table. This list box lets you select one or more entries by holding down the Shift key to select a contiguous range or by holding down the Ctrl key to select several noncontiguous entries. When you click the Edit button, a Visual Basic procedure evaluates your choices and opens the frmContacts form (see Figure 10–2) to display the selected contacts.
Figure 10–10: A list box on the frmContactList form allows you to choose multiple contacts to edit.
A list box like this one can use data from more than one field. In fact, the query behind this list box returns both the ContactID field (the primary key) from tblContacts and the expression that you see containing last name, first name, and middle initial. The list box hides the ContactID, but Visual Basic code behind the form uses that hidden value to quickly find the contacts you want.
Combo boxes are similar to list boxes. The primary difference is that a combo box has both a text box and a drop-down list. One major advantage of a combo box is that it requires space on the form only for one of the values in the underlying list. However, you can choose only one value in a combo box.
The PostalCode field in the frmCompanies form (see Figure 10–6) is set using a combo box, as shown in Figure 10–11. The combo box uses four fields from the underlying query-the Zipcode, City, State, and County fields from the lookup query qlkpZips. When you select a postal code, the combo box sets the PostalCode field in the underlying record-a very useful feature. Visual Basic code attached to this control also automatically copies the related City, County, and State data from the selected row in the combo box to the fields on the form for you. As long as you know the postal code, you don’t have to enter the other related information. You’ll find a similar combo box used in the application wherever you need to enter a postal code on a form.
In most cases, you will choose settings that disallow choosing a value that’s not in the list in your combo boxes that update fields. You can also write Visual Basic code to examine a new value that a user tries to enter and determine whether it should appear in the list. You can learn about how to create code to deal with “not in list” values in Chapter 20.
Figure 10–11: When you click the arrow on a combo box, you can see a list of options.
In Chapter 5, “Modifying Your Table Design,” you learned about Multi-Value Lookup Fields. When you define a field as a Multi-Value Lookup Field, Access 2007 provides a special control in the Datasheet view of the table and on a form similar to a combo box to display the valid list of values. When you click the arrow, you’ll see a list box with a check box next to each of the available value choices. Selecting the check box next to one or more of the values stores the selected values in the field.
The ContactType field in the frmContactsPlain form is a Multi-Value Lookup Field. Open this form and you can see a Contact Type field with an arrow on its right side. Clicking the arrow opens the list of available choices for Contact Type, as shown in Figure 10–12. You can see that John Viescas is designated as both a Developer and a Distributor. You can have Access store multiple values for this single record by selecting the check box next to an available contact type.
Figure 10–12: The Contact Type field on the frmContactsPlain form is a Multi-Value Lookup Field control.
Earlier in this chapter, you saw that one way to deal with the need to display lots of information on one form is to use a multiple-page form (frmContactsPages, shown in Figure 10–2 and Figure 10–3). Another way to organize the information on a single form is to use the tab control to provide what look like multiple folder tabs that reveal different information depending on the tab chosen-much like the main Ribbon in Access 2007 provides Home, Create, External Data, and Database Tools tabs. In the Conrad Systems Contacts database, a contact has basic contact information and notes, as well as related companies, contact events, and products. Open the frmContactsPlain form to see how the tab control displays only one of these types of information at a time, as shown in Figure 10–13.
Figure 10–13: When you first open the frmContactsPlain form, you see information on the Contact Info tab.
You can click the Companies tab (as shown in Figure 10–14) or any of the other tabs to see additional information. Note that there’s no programming required to implement tab selection and data display. See Chapter 13 for details about how to use the tab control.
Figure 10–14: When you click another tab in a complex form you can see different data.
In Chapter 4, “Creating Your Database and Tables,” you learned about the new Attachment data type to store complex data. Access 2007 includes a new type of control, called an attachment control, to add and delete data from this data type. If you still have the frmContactsPlain form open, click the Contact Info tab to see the contact’s picture displayed on the right side of the form. The picture is stored in an attachment field in the contact’s record. On the frmContactsPlain form you can use the attachment control to add and delete the contact’s picture. When you right-click on the attachment control, Access shows a shortcut menu with Forward, Back, and Manage Attachments commands, as shown in Figure 10–15.
Figure 10–15: Right-click on an attachment control to see a shortcut menu with a Manage Attachments command.
The Forward and Back commands are unavailable because there is only one attachment assigned to the attachment field in this record. Select Manage Attachments and Access displays the Attachments dialog box, as shown in Figure 10–16.
Figure 10–16: You can add and delete different data files bound to an Attachment data type using the Attachments dialog box.
The left side of the Attachments dialog box lists all the files stored in the attachment field in the current record. The Add button opens the standard Windows Choose File dialog box where you can browse to another file to attach to this field. The Remove button deletes the attachment selected on the left side from the attachment field in the current record.
The Open button opens the selected attachment using the application that’s defined in the Windows registry as the default application for this type of data. (On a computer running Windows Vista, the .jpg file opens in the Windows Photo Gallery program unless you have installed another program to view and edit pictures.) The Save As button opens the Save Attachment dialog box where you can save the selected attachment to a folder. The Save All button functions the same as Save As, except that you can save all the attachments (if there are more than one) in this attachment field to a folder in one step.
Use the record navigation buttons to move to Jeff’s record, click the Add button to open the Choose File dialog box, browse to the Documents subfolder where you installed the sample files, and select the Microsoft Office Word document for Jeff called JeffConrad.docx. After you select the file, click Open to add the file to the attachment field in the current record. You can see the additional file listed in the Attachments dialog box, as shown in Figure 10–17.
Figure 10–17: Jeff’s document has now been added to the attachment field in the current record.
Click OK in the Attachments dialog box to return to the frmContactsPlain form and notice that you still see only one picture displayed for this record. Right-click the attachment control again. You can now use the Forward and Back commands on the shortcut menu to view the two different files saved in the attachment field in the current record, as shown in Figure 10–18.
Figure 10–18: When you store multiple files in an attachment field, you can use the Forward and Back commands on the shortcut menu to view the files.
To demonstrate an ActiveX object, close the Contacts.accdb database and then open the Contacts2Upsize.accdb database. Open the frmContactsPlain form in this database from the Navigation Pane to see a form that is nearly identical to frmContactsPlain in the Contacts.accdb database. Unlike the contact picture you saw earlier on the frmContactsPlain form in the Contacts.accdb database, this picture is stored in a field in the tblContacts table using Microsoft’s ActiveX technology.
The Contacts2Upsize.accdb database is a version of the Conrad Systems Contacts application that has been modified to upsize the data to SQL Server and the application code to an Access project file (.adp). Because SQL Server cannot store the data in attachment fields, we changed the Photo field in the tblContacts table from the Attachment data type to the OLE Object data type. Also, we changed the control on the forms displaying the Photo field from an attachment control to a bound object frame control.
The logo in the top part of the main switchboard form (frmMain) in the Conrad Systems Contacts database, on the other hand, is a picture that Access has stored as part of the form. The control that you use to display a picture or any other ActiveX object is called an object frame. A bound object frame control is used to display an ActiveX object that is stored in a field in a table-such as the picture on frmContactsPages or frmContactsPlain in this database. When you edit the object in a bound object frame, you’re updating a field in the table. Use an unbound object frame or an image control to display an object that is not stored in a table. Access stores the object with the form definition, and you cannot edit it in Form view.
When you include a bound object frame control on a form and bind the control to an OLE object field in the database, you can edit that object by selecting it and then rightclicking the picture to open the shortcut menu. On the Bitmap Image Object submenu, select Edit, as shown in Figure 10–19.
Figure 10–19: You can select a picture and then edit it by selecting Bitmap Image Object on the shortcut menu and then selecting Edit on the submenu.
If you use an unbound object frame or image control on a form, you can edit the contents of the control only when you have the form in Design view.
The Contacts2Upsize application also provides handy New Photo, Edit Photo, and Delete Photo buttons on the frmContactsPlain form. When the form loads, it examines your Windows registry and determines the default program on your computer to edit bitmap (.bmp) or JPEG (jpg) files. When you click the New Photo or Edit Photo button, Visual Basic code behind the form starts that program for you. You don’t have to worry about navigating the complex shortcut menu.
If the object is a picture, a graph, or a spreadsheet, you can see the object in the object frame control and you can activate its application by double-clicking the object. If the object is a sound file, you can hear it by double-clicking the object frame control.
Figure 10–19 shows one of the photographs stored in the tblContacts table that is bound in an object frame control on the frmContactsPlain form. When you double-click the picture-or select the picture, right-click, select Bitmap Image Object from the shortcut menu, and then select Edit from the submenu-Access starts the default application on your computer to edit bitmaps. On most computers, this is the Microsoft Paint application. In Windows, Paint is an ActiveX application that can “activate in place,” as shown in Figure 10–20. You can still see a few Access commands listed on the Paint File menu, but Paint has added its own toolbars and menu commands. You can update the picture by using any of the commands on the Paint toolbars and menus. You can paste in a different picture by copying a picture to the Clipboard and clicking the Paste command on Paint’s Edit menu. After you make your changes, simply click in another area on the Access form to deactivate Paint and store the result of your edits in the object frame control. If you save the record, Access saves the changed data in your OLE object field.
Figure 10–20: The Photo OLE object field from Figure 10–19 is being edited “in place” with its host application.
If you have registered an application other than Microsoft Paint to handle bitmap objects, that application will be activated when you select Edit from the submenu.
Another useful control is the command button, which you can use to link many forms to create a complete database application. Close the Contacts2Upsize.accdb database and return to the Contacts.accdb database. In the Conrad Systems Contacts database, for example, most of the forms are linked to the main switchboard form (frmMain), shown in Figure 10–21, in which the user can click command buttons to launch various functions in the application. The advantage of using command buttons is simplicity-they offer an easy way to trigger a macro or a Visual Basic procedure. The procedure might do nothing more than open another form, print a report, or run an action query to update many records in your database. As you’ll see when you get to the end of this book, you can build a fairly complex application using forms, reports, macros, and some simple Visual Basic procedures.
Figure 10–21: The command buttons on the frmMain switchboard form take the user to various parts of the application.
In Chapter 8, you learned how to create the PivotTable or PivotChart view of a query. You can also build a form that is connected to a table or query and switch to either PivotTable or PivotChart view to define a custom view of the underlying data. For example, take a look at the ptContactProducts form shown in Figure 10–22. This form is designed to open only in PivotTable view or Design view. Note that even though the query on which this form is based is updatable, you cannot update any of the field values via the PivotTable.
Figure 10–22: The ptContactProducts form is designed to open in PivotTable view.
PivotCharts can be useful in an application to provide a related graphical representation of data displayed on a form. In the Conrad Systems Contacts sample database, you can find the chtProductSales form that charts sales by product and by month. This form is embedded in a report that displays product details (rptProductSalesByProductWChart) and in a sample form that lets you edit product information while viewing the related past sales data as a chart (FrmProductsWithSales). You can see FrmProductsWithSales in Figure 10–23.
Figure 10–23: This form to edit product data also has an embedded subform in PivotChart view to show related sales information.
Designing a form as a PivotTable or PivotChart has three distinct advantages over performing these functions in queries.
You can restrict the views of the form to display only the PivotTable or the PivotChart or both. You can set a default view for a query, but you cannot prevent the user from switching to Datasheet or Design view.
You can embed a form designed as a PivotTable or PivotChart in another form or in a report to display related information. You cannot embed a query in a form or report.
You can write Visual Basic code behind the form to dynamically modify the PivotTable or PivotChart. You can also restrict the changes a user can make to the table or chart. You cannot write code behind a query.
You’ll learn more about designing forms as PivotTables or PivotCharts in Chapter 13.