Special Edition Using Microsoft Office Access 2003

The data editing form you created in the preceding sections is adequate for use by experienced users, but adding data entry validation and lookup lists for queries makes the form easier to use. You also are likely to be faced with the need to change the primary data source for the form. The following sections cover these three topics.

Setting Local Data Validation Rules

If the schema for your form specifies XSD built-in datatypes of elements, such as decimal, int, boolean, date, or dateTime, InfoPath tests your query filter and data entry values for conformance to the requirements of the World Wide Web Consortium (W3C) XML Schema Part 2: Datatypes recommendation. XSD schema also can apply constraints to data values by facets, such as length, minLength, and maxLength for the string data type. Each datatype has its own set of facets. The built-in primitive and derived data types handle almost all Jet and SQL Server 2000 datatypes. (Jet's Hyperlink field data type is an exception; Jet Currency and SQL Server money fields convert to the decimal datatype.)

Note

You can read the W3C XML Schema Part 2: Datatypes recommendation at http://www.w3.org/TR/xmlschema-2/>

InfoPath doesn't attempt to determine the dataType or other constraints, other than maxLength of queryFields and dataFields subelements. You could alter the schema(s) for your form to add dataType, but the process isn't simple. ADO Recordsets use attribute-centric, not element-centric, XML. The nodes that appear to be elements under the q:customers and d:customers nodes in the Data Source list are attribute name/value pairs. If you mark the Show Details check box, InfoPath reports the data type as simpleType, which corresponds to XSD's anySimpleType datatype. Schema1.xml (for q:customers) and Schema2.xml (for d:customers) specify string as the data type of all fields.

To review the differences between element- and attribute-centric XML, see "Gaining an XML Vocabulary," p. 944.

InfoPath uses XML Path Language (XPath) 1.0 expressions to apply validation constraints to controls. Fortunately, you don't need to learn XPath syntax to add simple constraints, such as Jet's Required = Yes or Allow Zero Length = No. Adding constraints to numeric values is equally simple.

As an example, do the following to require CustomerID and CompanyName values for new Customers records:

1. Right-click the data entry section's Customer ID field, and choose Text Box Properties to open the Properties dialog.

2. Click the Data Validation button to open the Data Validation (FieldName) dialog, and click Add to display an expanded version of the dialog.

3. Select Is Blank from the middle (condition) list (see Figure 26.34).

   Figure 26.34. To prevent users from submitting a form with an empty field value, add the Is Blank validation rule to the field's text box.

4. Type a screen tip to appear when the text box value doesn't comply with the validation rule, and add an optional message box that contains an alternate or more descriptive validation error message (see Figure 26.35).

   Figure 26.35. Add screen tip and, optionally, message box text to describe the validation rule violation.

5. Accept the default Inline Alert, and click OK three times to close the dialogs.

6. Select the Company Name field and repeat steps 1 through 5, but change text in step 4 to require a company name.

7. Preview the form. Text boxes whose values don't conform to validation rules display a red underline and the screen tip.

8. Right-click the text box and choose Full Error Description to display the message box (see Figure 26.36).

   Figure 26.36. Text boxes with values that don't meet validation rules gain a red underline. Attempting to submit a form with a validation error displays an error message and draws a red rectangle around the control.

9. Return to template design mode, and press Ctrl+S to save your changes.

If you attempt to submit a form with a value that violates a validation rule, you receive an "InfoPath cannot submit the form because it has errors" message and a red dashed outline surrounds the offending text box or other control.

The Data Validation dialog lets you create compound validation rules that are similar to Access's table-level validation. You can combine validation rules for the same or different fields with and/or logic by clicking the And button and selecting And or Or in the right-most list (see Figure 26.37).

Tip

Open the Section Properties dialog for a section, click the Display tab, click the Conditional Formatting button, and then click the Add button to open the Conditional Formatting version of the Data Validation dialog. This dialog lets you change the background color of the section, the font color of the control, and other display attributes when users violate a validation rule.

Changing a Text Box to a Bound Drop-down List

InfoPath controls have a Change To context menu choice that emulates Access's Change To feature. You can change a bound text box to one of nine bound InfoPath controls. The most common change is from a text box to a drop-down list or date picker control for date or dateTime fields.

At the risk of creating an extraordinarily long list, you can change the CustomerID text box of the query field to a drop-down list of CustomerID values populated by a secondary data source. HTML lists don't have the flexibility of Access combo boxes, so you can't type a value into the list's text element. However, InfoPath thoughtfully adds an empty value at the top of the list, so users can apply a query filter without specifying a CustomerID value.

Creating the Secondary Data Source

Creating a secondary data source from a database is almost identical to creating the primary data source. For a drop-down list, you usually add only the field to which the text box is bound.

To add the CustomerID field as a secondary data source for a drop-down list on the form, do this:

1. In design mode, choose Tools, Secondary Data Sources to open the Secondary Data Sources dialog and click Add to start the Data Source Wizard.

   Tip

   Refer to steps 2 through 8 of the earlier "Creating a Dual-View Template from the Primary Data Source" section for a more detailed description of the following steps.

2. Choose the Database option in the First Wizard dialog and click Next.

3. Click Select Database, navigate to the ...\Samples folder, and double-click Northwind.mdb.

4. Clear all but the CustomerID check box and click Next.

5. In the final Wizard dialog, replace Customers with a more descriptive name, such as CustIDLookup, and click Finish and Close.

Replacing a Text Box with a Drop-down List

To replace the CustomerID text box of the query section with the drop-down list and specify the list's row source, do the following.

1. Right-click the query section's text box, choose Change To, and select Drop-Down List, which fills a line.

2. Adjust the width of the list to accommodate five characters.

3. Right-click the list and choose Drop-Down List Properties to open the dialog of the same name.

4. Select the Look up in a Database, Web Service or File option, which sets CustIDLookup as the value of the Data Source list.

5. Click the Select XPath button to the right of the Entries text box to open the Select Field or Group dialog (see Figure 26.38). Select either d:Customers (because the group has only one field) or :CustomerID and click OK.

   Figure 26.38. Select the data field or single-field group of the secondary data source that supplies the row source for the drop-down list.

6. Specifying d:Customers adds the XPath designation for the node and the @CustomerID XPath pointer to the single child node to the Value and Display Name text boxes (see Figure 26.39).

   Figure 26.39. The Entries text box contains the XPath expression that points to the d:Customers node; @CustomerID is the child node that populates the drop-down list.

   Note

   If you specify :CustomerID in step 5, Value and Display Name contain periods (.), the XPath self-reference.

7. Click OK to close the dialog, click Preview Form, and open the new list (see Figure 26.40).

   Figure 26.40. The new drop-down list lets users select from a long list of CustomerID values or an empty value that enables query filters based on City, Region, Country, or all three. This double-exposure shows the list to the right of the added drop-down list.

8. If you want to retain the drop-down list, save your changes. Otherwise, exit InfoPath and don't save the changes.

The entire XPath expression for the list entries, which isn't visible fully in Figure 26.39, is /dfs:myFields/dfs:dataFields/d:Customers. The dfs: qualifier presumably represents "data field source."

Tip

If you want to display values from another field, such as CompanyName, instead of the primary key value, include the field when you create the secondary data source. Change the XPath reference to the other field (@CompanyName) in the Drop-Down List Properties dialog's Display Name text box. You must increase the width of the list to make the company names readable.

Changing a Form's Primary Data Source

As mentioned earlier in the chapter, changing an InfoPath 1.0 primary data source isn't as easy as changing secondary data sources. To change the path or filename for a Jet data source or change from the Jet to an SQL Server version of the data source, you must manually alter the manifest file in Notepad and then refresh the template file with the altered manifest file. The process is similar to the changes you made to the Currency data source in the earlier "Changing a Secondary Data Source" section.

To change the location of a Jet primary data source, do the following:

1. Make a backup copy of your template (.xsn) file.

2. In template design mode, choose File, Extract Form Files, and save the extracted files to the folder with the template file. Overwrite any existing files in the folder.

3. Close InfoPath.

4. Open the manifest.xsf file in Notepad with WordWrap on, and search for .mdb to locate the <adoAdapter> element. This element contains the ADO connect string, the query that generates the data source, and the queryAllowed and submitAllowed attributes (see Figure 26.41).

   Figure 26.41. Manually edit in Notepad the path and, if necessary, name of the Jet .mdb file that contains the tables for the primary data source.

5. Edit the path and filename, as necessary, for each instance of the <adoAdapter> element. Each secondary data source adds an element.

6. Press Ctrl+S to save the file.

7. Right-click manifest.xsf and choose Design to open InfoPath from the manifest file.

8. Verify that queries and drop-down lists work correctly, and submit a temporary edit or new record to the table.

   Caution

   Don't add a new record to a table with an AutoNumber field if you're concerned about auditability. Deleting the record will create a gap in the AutoNumber sequence.

9. Open the table and verify that the temporary update was successful.

10. Undo the change you made in step 8 by an InfoPath Submit or Delete & Submit operation, and verify the undo operation in the table.

11. Choose File, Save As, click the Save button, and overwrite the TemplateName.xsn file.

12. Repeat steps 8 through 10 with a form opened from the .xsn file to verify update of the template archive.

If you don't explicitly overwrite the TemplateName.xsn file, you might find that the changes you made to manifest.xsf aren't reflected in the template file.

Tip

If you want to move the primary data source from Jet to an upgraded SQL Server 2000 database, create in a different folder a temporary template from the SQL Server data source and extract the manifest.xsf file. Copy the entire contents of each <adoAdapter> element and paste-replace the corresponding element in the original manifest.xsf file.