Special Edition Using Microsoft Office Access 2003

Microsoft Office InfoPath 2003 called XDocs during its early beta period is a forms-based XML document generation and editing application. InfoPath's primary application is producing structured data from common business forms, such as expense reports, employee performance reviews, vendor evaluations, time cards, and sales call reports. The structure of the data is defined by an underlying XML schema, which validates users' entries in the form's controls text boxes, option buttons, drop-down lists, and check boxes. When you design a form from scratch, InfoPath generates the schema for you. InfoPath also can infer a schema from an existing XML document. These features mean that you don't need to be an XML expert to produce useful data entry and editing forms with InfoPath 2003.

InfoPath forms share many of the features of Access's data access pages (DAP), such as a design surface on which you place controls that are bound to an underlying data source. InfoPath, however, is self-contained and doesn't rely on the Office Web Components for data binding, record navigation, and editing. You display and edit data in InfoPath sections or repeating sections, which correspond to Access's bound forms and subforms. DAP and InfoPath use VBScript or JScript event handlers to customize form behavior.

InfoPath 2003 binds to the following types of data sources:

• Jet tables or queries

• SQL Server tables, views, table-returning functions, and stored procedures

• XML documents stored as .xml files

• Document/literal XML Web services

For a definition of document/literal XML Web services, see "SOAP Message Formats," p. 1336.

This is an Access 2003 book, so the InfoPath form you create later in this chapter connects to Jet .mdb files or SQL Server 2000 databases with OLE DB and ActiveX Data Objects (ADO). InfoPath automates the form generation process for ADO Recordsets, which means that you don't need to be a full-fledged InfoPath developer or programmer to take advantage of this new Microsoft Office member. Displaying and updating relational data, however, won't be InfoPath 2003's primary application for most organizations. InfoPath's most common use probably will be creating and editing XML data documents. Thus, this chapter begins with an overview of basic form completion and design techniques for common XML business documents.

Another promising InfoPath application is providing a simple user interface for basic XML Web services. Web services initially were intended for server-to-server communication. A recent trend is employing Web services to deliver business information from databases as messages containing structured XML documents. InfoPath 2003 can handle forms-based data display and database updates with simple XML Web services.

Note

To learn more about WSR 2.0, see "Creating an Access Web Service Consumer," p. 1342.

Getting Acquainted with InfoPath Forms

InfoPath 2003 offers a set of sample templates for common business forms, just as Access 2003 includes templates for a variety of useful business-related databases. The sample templates let you experiment with InfoPath form features such as hidden (optional) sections, drop-down lists populated by a secondary data source, and expanding lists. Drop-down lists correspond to Access's bound combo boxes; the secondary data source is equivalent to the Row Source property. An expanding list emulates an Access subform.

To give InfoPath a test drive with a sample form, do the following:

1. Launch Microsoft Office InfoPath 2003, which opens by default with the Fill Out a Form task pane active.

2. Click the More Forms task pane link to open the Forms dialog, select a form to fill in (see Figure 26.1), and click OK to display the form Change Order for this example. The first form you open from any template is Form1. Optionally, press Ctrl+F1 to redisplay the task pane.

   Figure 26.1. InfoPath offers 25 sample business forms and associated schemas that you can alter to suit your requirements. All sample forms share a common design pattern.

3. Type sample data in the form. A dashed rectangle surrounds fields contained in form sections. Some sections are optional, as indicated by a button to the left of the heading (see Figure 26.2).

   Figure 26.2. As you fill in the form, a blue border identifies the active text box. A blue dashed line surrounds individual sections; optional sections have a button that lets you delete them from the form.

   Note

   Despite the Remove SectionName drop-down menu choice, you can't remove some subsections because doing so violates the schema's requirements.

4. Continue completing the form until you reach the Reason for Change section. Click the button to open the section's drop-down menu and select Remove Reason for Change. When you remove an optional section, an orange circle with a icon and a Click Here to Insert the SectionName Section caption replaces the hidden section.

5. Open the Currency list to select a currency from the 100 or so choices. A secondary data source populates the currency list for all forms.

6. Remove the Detailed List of Changes section and type a description and value in the Itemized Changes list. JScript code behind the form maintains running totals of Cost and Hours.

7. With the first entry in the Detailed List of Changes selected, press Ctrl+Enter or open the list's drop-down menu, and select Insert Below to add a new line to the section. When you add new Cost and Hours values, the totals update (see Figure 26.3).

   Figure 26.3. Users can modify the form by removing optional sections, specify currency, and add line items to lists.

8. Press Ctrl+S to save the form. If you store the form in My Documents specify a memorable name, such as ChangeOrder1.xml. By default, saving the form doesn't close it.

The XML document you create when saving a form contains a set of processing instructions (PIs), which are highlighted in Figure 26.4. These instructions cause Internet Explorer (IE) 6+ to launch InfoPath, if necessary, and open the form in the template specified by the Universal Resource Name: urn:schemas-microsoft-com:office:infopath:oob:ChangeOrder:1033. Thus, you must use Notepad or another text editor to read the XML form document.

The remainder of the form document consists of optional elements defined by the form's underlying schema(s). Many elements are empty because the form doesn't contain text boxes or other controls bound to these elements.

Note

Exporting and Mailing InfoPath Forms

In addition to saving the form, you can export the form as a single FormName_View 1.mht Web page or to an Excel 2003 worksheet by choosing File, Export To. The .mht page is a read-only replica of the completed form. Command buttons that execute InfoPath's built-in operations such as Run Query, New Record, and Submit or custom scripted functions don't appear on .mht pages.

The Export to Excel Wizard lets you create a new workbook from the form's XML data. To save the form data in Excel, do the following:

1. Choose File, Export to, Microsoft Excel to start the Wizard. Click Next.

2. Select the Form Fields and This Table or List option, and select the table(s) or list(s). For this example, the only list is Itemized Changes Entry (see Figure 26.5). Click Next.

   Figure 26.5. The second Export to Excel Wizard dialog lets you add data from form tables or lists to the worksheet.

3. The third Wizard dialog selects all elements that have values for export (see Figure 26.6). Deselect any items you don't want to include and click Next.

   Figure 26.6. Select the fields you want to include as worksheet columns in the third Wizard dialog.

4. In the last Wizard dialog, accept the Export Data from This Form Only, unless you've saved other forms from the same template.

5. Click Finish to generate the worksheet, which contains a headings row and the two rows that represent the two items you added to the Itemized Changes Entry list (see Figure 26.7).

   Figure 26.7. Rows of worksheets generated from InfoPath forms that include lists display all form fields as columns.

The obvious benefit of exporting InfoPath forms to Excel is the ability to summarize data from multiple forms based on a single template. You can use Access's import or link table features to create flat tables from the summary worksheet.

To review the process for importing or linking Excel files, see "Importing and Linking Spreadsheet Files," p. 284.

Choosing File, Send to Mail Recipient lets you email a copy of the form to one or more recipients (see Figure 26.8). If the recipient has InfoPath installed, she can edit the form and pass it on to others. Users without InfoPath receive a read-only form copy that's identical to the .mht Web page.

Note

Most of the sample forms have signature blocks at the bottom of the page, which apply to printed forms only. InfoPath has a Tools, Digital Signatures menu choice that opens a dialog to let the originator digitally sign the form to prove its authenticity. Others in the approval workflow can add their digital signatures to authenticate the form's status.