Designing an InfoPath Query and Data Editing Form

InfoPath has a set of built-in operations for database query and editing operations. When you specify a database table or query as the primary data source for the form, InfoPath automatically generates a form with two views of the data. The query view is remarkably similar to Access's Filter by Form feature. You type one or more exact values to match in the view's text boxes; the query view doesn't accept wildcards. Data entry view displays records that match the query values. You can restrict the data entry view to display a single record or deliver multiple records to a repeating section. If the data source is updatable, you can add new records to the table(s) in data entry view.

InfoPath lets you save query and data entry views locally in the form's XML document. Local storage lets users work with the data while they're offline, edit the data, and then submit the changes when they reconnect to the network. In this respect, InfoPath data editing process emulates ADO's batch-optimistic updating feature.

For more information on batch-optimistic updates, see "Taking Advantage of Disconnected Recordsets," p. 1312.

InfoPath offers the following three pre-defined command buttons for database operations:

• Run Query applies the filter you define in query view text boxes to the ADO Recordset and returns the filtered result set to the data view.

• New Record generates a data entry view with an empty form for adding a record to the table(s).

• Submit sends edits to existing data or a new record to the record source's table(s). InfoPath applies local validation rules to the entries. Submission fails if the database rejects the changes.

• Delete & Submit deletes the current record if deletion doesn't violate referential integrity or table/field validation constraints.

Figure 26.19 shows the InfoPath form similar to the one you create in the following sections. The form connects to Northwind.mdb's Customers table and lets users perform UPDATE, INSERT, and DELETE operations. The form is restricted to operating on a single record, so query and data entry operations appear in a single view. Using a single view simplifies use of the form.

Figure 26.19. This data editing form combines query and data editing operations in a single view. Spell checking is enabled by default for InfoPath query and data entry views.

Note

The completed Northwind Traders Customers Editing Form template and its associated files are located in the \Seua11\Chaptr26\NWCustEditSingle folder of the accompanying CD-ROM. The NWCustEditSingle.xsn template expects Northwind.mdb to be in the default C:\Program Files\Microsoft Office\Office11\Samples folder and will fail if the database is located elsewhere.

If you encounter an "InfoPath cannot create a new, blank form" error when you attempt to open see the "Changing the Publishing Point of an InfoPath Template" topic of the "Troubleshooting" section near the end of this chapter.

Creating a Dual-View Template from the Primary Data Source

Generating the initial form template from an Access or SQL Server 2000 database is a simple, Wizard-based process. Before you modify Northwind.mdb's Customers table with InfoPath, make a backup copy of the table.

Tip

Be especially careful to follow the exact instructions for setting up the primary data source. Unlike secondary data sources, there's no simple method for changing the primary data source or its properties once you complete the process.

To create the standard query view for the Customers table, do the following:

1. Open InfoPath and click the Design a Form link in the task pane's default Fill Out a Form pane.

2. Click the New from Data Source link of the Design a Form pane to start the Data Source Setup Wizard.

3. Select the Database (Microsoft SQL Server or Microsoft Access Only) option, and click Next.

4. In the second Wizard dialog, click Select Database to open the Select Database dialog, navigate to ...\Office11\Samples, and double-click Northwind.mdb to open the Select Table dialog, which also displays Access queries.

5. Scroll to and double-click the Customers table to return to the Wizard dialog, which displays the table's field list with all fields selected for inclusion in the query and data views (see Figure 26.20).

   Figure 26.20. The second Data Source Setup Wizard dialog lets you select fields for the views and modify the SQL statement that generates the primary data source.

   

6. Click Modify Table to open the Sort Order dialog. You don't need to sort the table, but returning a single record is important to the design of the form. Clear the Allow Multiple Records from This Table to be Displayed in the Form check box (see Figure 26.21). Click Finish.

   Figure 26.21. The Sort Order dialog also includes the check box that you clear to limit the query result set to a single record.

   

7. Click Edit SQL to view the SQL statement that InfoPath uses to generate the XML schemas for the two views. When you click Test SQL Statement, a message indicates that the query satisfies InfoPath's requirements (see Figure 26.22). Click OK twice to return to the Wizard, and then click Next.

   Figure 26.22. The SQL statement doesn't include the expected SELECT TOP 1 statement to limit the query to a single record. InfoPath uses the query to generate schemas only.

   

8. The third Wizard dialog confirms your choices, except the single-record restriction. By default, the Wizard generates the query view first (see Figure 26.23).

   Figure 26.23. You can specify the sequence of view generation in the final Wizard dialog.

   

9. Click Finish to generate and display the query view in design mode. Expand the nodes in the Data Source task pane to display the queryFields and dataFields members. Mark the Show Details check box to display the XML Schema (XSD) data type of the fields (see Figure 26.24).

   Figure 26.24. The Wizard generates a query view with all fields in one cell of a single-column table contained in the Customers section.

   

The query view has a New Record button at the top of the form and a Run Query button at the bottom, but the Wizard doesn't add a section with edit controls to the default data entry view. Why the Microsoft developers didn't populate the default entry view is a mystery.

You must add a temporary editing section to data entry view and run a sample query to test your database connection and verify that query view behaves as expected.

To add a data editing section to the temporary data entry view, do this:

1. Choose View, Manage Views to display the Views task pane, and click Data Entry (default) to open an empty view.

2. In the Views task pane, click the Data Source link, and scroll to the dataFields node.

3. Drag the d:Customers node to the empty view, release the mouse button, and select Section with Controls from the context menu to create a section that's identical to query view (see Figure 26.25). Unlike query view, labels in data entry view display the field's Caption property value, not the field name.

   Figure 26.25. The Wizard generates a query view with all fields in one cell of a single-column table contained in the Customers section.

   

4. Click Preview Form, choose View, Query and type a valid CustomerID value, such as ALFKI, in the text box.

5. Scroll to the bottom of the form and click Run Query. Data for the selected record appears in the section controls you added in step 4 (see Figure 26.26).

   Figure 26.26. This data entry view is temporary. You use it to test the connection to the database and query (filter) execution only.

   

6. Choose View, Query to return to query view and click New Record to verify that the data entry view opens with empty text boxes.

7. Click Close Preview to return to design mode.

Note

If you click Run Query with no filter values or with a filter that returns more than one record, you receive an "InfoPath cannot run the specified query" error message. Supplying a filter value that doesn't match a record displays a "The specified query did not return any data" message.

Combining Query and Data Entry Operations in a Single View

The design and form completion processes for a dual-view data entry form aren't intuitive, to be generous. For example, the data entry view should include auto-generated Submit and Query View buttons. Submit is a built-in button that you can add to the form easily, but Query View isn't. Thus most data form designers probably will choose a single-view approach.

Note

Adding a Query View button requires writing a JScript or VBA event handler to change the view.

Single views require compact query and data entry sections to minimize scrolling when editing data. Compact presentation is especially important for forms that display multiple records. The lack of wild-card support for queries makes many fields, such as Company Name, inappropriate for searching because you must type the search term exactly (other than case). CustomerID, City, Region, and Country should satisfy most users' query requirements.

To remove the data entry view and redesign the form to a compact, single-view version, do the following:

1. In the Views task pane, right-click Data Entry (default), choose Delete, and acknowledge the warning message. Query becomes the default view.

2. Replace Query Form with an appropriate title, in this case Northwind Traders Customers Data Entry Form or the like. Select and delete the table row under the title row.

3. Select the CompanyName, ContactName, ContactTitle, and Address labels and text boxes and delete them. Do the same for PostalCode and Phone and Fax.

4. Delete the line feed after each text box and add a space between the text box and the adjacent label.

   Tip

   If you want to increase the width of the form to permit wider text boxes, right-click each table and section, choose properties, and change the width from the default 625 px to 700 px or greater.

5. Adjust the width of the text boxes to accommodate common filter values. The objective is to reduce the query section's table to a single row of labels and text boxes.

6. Click the Data Source link to display the Data Source task pane, select the d:Customers node under the dataFields node, drag the node to the right of the Run Query button, release the mouse, and select Section with Controls from the menu. Remove the extra line feeds at the bottom of the new section (see Figure 26.27).

   Figure 26.27. Removing unneeded filter parameters and rearranging the controls increases the region available for the data entry section.

   

7. Click the Preview Form button to display the Query view, type a filter parameter or two, and click Run Query to test your work so far (see Figure 26.28).

   Figure 26.28. Running a query in form preview mode demonstrates that the data entry section of query view works as expected.

   

8. Return to design mode and repeat steps 4 and 5 for the fields in the data entry section to reduce its height. You can eliminate unneeded words in the field labels to conserve space.

9. Buttons for data entry operations should be located adjacent to the data entry section, so exchange the location of the Run Query and New Record buttons.

10. Save your template with a descriptive name in a folder of its own. If you save multiple forms in the same folder, extracting the individual template files overwrites those of other templates in the folder.

Adding Pre-Built Buttons to the Form

The InfoPath toolbox has a collection of conventional HTML elements, such as text, check, list, and drop-down list boxes, button, option button, text lists of various types, hyperlink, and picture. InfoPath-specific elements consist of a rich text box, various types of sections, repeating table, and expression box.

To make date entry operations function, you must add at least a Submit button to update the form's underlying table(s) with edits or new records. Adding a Delete & Submit button is optional. Adding buttons demonstrates how you set the buttons to perform pre-built data entry actions.

To add both buttons to your template, do the following:

1. In design mode, click the Controls link on the task pane to display the Insert Controls list.

2. Scroll to the Button control and drag it to the right of the New Record button.

3. Right-click the new button, and choose Properties to open the Button Properties dialog (see Figure 26.29). The default action for the button is to create a JScript onClick event handler.

   Figure 26.29. The Button Properties dialog lets you specify the action that occurs when the user clicks the button.

   

   Caution

   Be careful not to select Script when modifying the button. With Script selected, clicking OK or Microsoft Script Editor creates an onClick event handler for the button, which attaches script.js to your project. Deleting the button doesn't remove the event handler, so attempts to preview the form throw a script error. To eliminate the error, you must open the Script Editor and delete the onClick function manually.

4. Select Submit from the Action list to open the Submitting Forms dialog, which lets you specify what happens after the user submits the form. You can select the submission method from the Submit list; the default, Submit to a Database applies to this example. Be sure that the Enable the Submit Menu Item on the File Menu check box is marked (see Figure 26.30).

   Figure 26.30. The Submitting Forms dialog lets you customize the submit operation.

   

   Tip

   If the Enable the Submit Menu Item on the File Menu check box is cleared, you receive an "InfoPath cannot submit the form data because the form template does not support submit" error. This error occurs despite your having selected the Enable Submit option.

5. Click Submit Options to open the dialog of the same name that lets you choose what happens to the form after successful submission and specify custom success and failure messages (see Figure 26.31).

   Figure 26.31. The Submit Options dialog enables further customization of the submittal operation.

   

6. Click OK three times to close the dialogs.

7. Repeat steps 2 and 3, but add the new button to the right of Submit and select Delete & Submit from the Action list. The settings you apply in the Submitting Forms and Submit Options dialogs to the Submit built-in button also apply to the Submit & Delete button.

8. Add some spaces between the three buttons and choose Preview Form to test submission of edits and new records (see Figure 26.32). A message box requests conformation of each submittal request.

   Figure 26.32. This example of adding a new Customers record demonstrates the custom success message shown in Figure 26.31.

   

9. Press Ctrl+S to save your changes.

You must add a new record to enable testing Delete & Submit, because all sample Customers records have dependent Orders records. If you submit an invalid update request, InfoPath returns an error message; in some cases, the errors originate from the database server. Some error messages have a Details button to open a text box that displays full text of the InfoPath or ADO error message.

The NWCustEdit.xsn template file is located in the \Seua11\Chaptr26\NWCustEdit folder of the accompanying CD-ROM. This template connects to Northwind.mdb in its default location C:\Program Files\Microsoft Office\Office11\Samples.

If you encounter an "InfoPath cannot create a new, blank form" error when you attempt to open a form, see the "Changing the Publishing Point of an InfoPath Template" topic of the "Troubleshooting" section near the end of this chapter.

Disabling Spell-Checking of Text Box Values

Spell-checking fields whose values aren't likely to appear in Office 2003's dictionary such as CustomerID and CompanyName is likely to distract users of your forms.

To turn off spell-checking for a text box control, open the Properties dialog for the control, click the Display tab, and clear the Enable Spelling Checker and Enable Auto-complete check boxes (see Figure 26.33). Unlike Access, you can't multi-select InfoPath controls and change a common property value; you must alter the property value for each control on the form.

Figure 26.33. Disable spell-checking of text box values by clearing the Enable Spelling Checker check box on the Display page. Disabling auto-complete also is a good practice for data entry forms.

Tip

Click the Advanced tab of the text box's Properties dialog to add a screen tip to or change the tab order of the text box.