|
|||||||
When you connect an InfoPath form to an Access database, you can choose whether you want to set up the database as the form's main data source (data source: The collection of fields and groups that define and store the data for an InfoPath form. Controls in the form are bound to the fields and groups in the data source.) or as the form's secondary data source (secondary data source: An XML data file, database, or Web service that is used by a form for the entries in a list box or for script actions.). If you want to be able to edit and add records to the database by filling out fields in the InfoPath form, you should set up the database as the form's main data source. This article explains how to set up a database as the main data source for a form, which includes how to do the following:
Setup requirementsTo complete the lessons in this article, you need to have Access 2003 and the Northwind sample database for Access installed on your computer. By default, the sample database is installed on your computer in the following location: C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\. Note If you cannot locate the Northwind sample database on your computer, you might need to install it. To do so, open Access, point to Sample Databases on the Help menu, and then click Northwind Sample Database. Lesson 1: Creating the formThis lesson shows you how to create a new InfoPath form, and then how to use the Data Source Setup Wizard to connect your form to the Northwind sample database in Access. When you have completed this lesson, your form will be connected to two tables in the Northwind sample database, and you will be able to add, edit, and query records from those tables. To use an Access database as the primary data source for an InfoPath form, you must start with a new form. You cannot modify an existing form to add a connection to a primary data source. Use the following procedure to create a new InfoPath form based on the sample database in Access:
By selecting the Suppliers table first, you are setting up the Suppliers table as the primary table for the data connection (data connection: The connection between an InfoPath form and an external data source, such as a database, Web service, SharePoint library, or XML file. Data connections are used to query and submit data.). Now you need to add the Products table to the data connection. Together, these tables create a one-to-many relationship, which means that each supplier provides multiple products. In Access, this relationship is defined by using a key field, which is a field that associates the records in one table with the records in another table. In this one-to-many relationship, the table on the “one” side contains a primary key field, which uniquely identifies each record in the table. The table on the “many” side of the relationship contains a foreign key field, which matches the other table’s primary key. The primary key and the foreign key are often given the same name. In this case, the Suppliers table contains the primary key field SupplierID, and the Products table contains the foreign key field SupplierID. When connecting your form to multiple tables in a database, InfoPath requires that the tables be connected by key fields. In addition, the primary table in the data connection must have either a one-to-many or one-to-one connection with any additional tables that you add. Now that you have added the primary table to your form, you can add your additional table.
By default, InfoPath connects to every field in a table. However, you might want to exclude a field, either because it uses a data type (data type: Property of a field that defines the kinds of data the field can store. Examples of data types include Text, Rich Text, Whole Number, Decimal, True/False, Hyperlink, Date and Time, and Picture.) that InfoPath cannot connect to or because you do not want to work with a certain field's values in your form. In this lesson, you will need to exclude the Homepage field because it has a Hyperlink data type, which InfoPath does not support. The Hyperlink data type, like the Memo data type, is a long data type; therefore, InfoPath cannot connect to it.
Before finishing the Data Source Setup Wizard, you need to verify that the data connection you created was established correctly.
After you have verified that the information in the Summary box is correct, you must specify which view (view: A form-specific display setting that can be saved with a form template and applied to form data when the form is being filled out. Users can switch between views to choose the amount of data shown in the form.) in your form you want to design first: the query view or data entry view. In the query view, you can enter values to query the database. In the data entry view, you can add new records to the database and edit existing records.
The resulting form should contain a blank view. Before you go on to the next lesson, you should save the form.
Lesson 2: Reviewing the InfoPath data sourceWhen you create a primary data connection between an InfoPath form and an Access database, InfoPath creates a data structure based on the structure of the Access database. Specifically, InfoPath creates fields and groups that match the fields and tables in the database. For example, InfoPath creates a Products group in the data source that corresponds to the Products table in Access. For each field in the Products table, there is a corresponding field in the InfoPath data source. Note Because InfoPath stores the data before submitting it to Access, you can enter information into the form For primary data connections, InfoPath actually creates two very similar data structures: a set of query fields and a set of data fields. You can use the query fields to enter values into your form and query the database. You can use the data fields to enter new records and modify existing records in the database. Each set of fields matches the tables and fields in the database. To better understand how the InfoPath data structure relates to the Access data structure, it is helpful to look at the data fields in your form.
The resulting data structure should look like this: |
The Suppliers group contains the Products group. The Suppliers table has a one-to-
many relationship to the Products table.
For each table in the Access database there is a group in the InfoPath form. For each field in the Access tables there is a field in the InfoPath group. In addition, the groups and fields have the same relationships as the fields and tables
Lesson 3: Laying out the form
Now that you have created a form based on the Access sample database and familiarized yourself with the InfoPath data source, you can begin laying out your form. To create the controls into which users type information, you can drag the fields and groups from the Data Source task pane onto your form. When you drag a single field onto your form, it creates a single control, such as a text box. When you drag a group of fields onto your form, InfoPath creates a group of controls, such as a section that contains a set of text boxes.
The type of control that is added to your form and its behavior depends on the type of field, which in turn is based on a particular field in the Access database. For example, an Access field with a Date data type results in an InfoPath field with a Date data type. If you drag that field onto the form, InfoPath creates a Date Picker by default.
In the next step, you will add the full group of dataFields to your form, which will result in a full set of controls on your form. You can then remove any unnecessary controls. Some controls are unnecessary because they contain redundant information; others contain information that does not apply to the two tables that you are using. For example, the Category ID text box is not necessary because it is used to establish a relationship with a Category table, which you are not using in this data connection.
Note Deleting a control does not delete the underlying field in the data source. For example, when you delete the Category ID text box, the CategoryID field still exists (and can contain data) in the data source.
- In the Data Source task pane, right-click the dataFields group, and then click Section with Controls on the shortcut menu. This inserts controls for all of the fields that you use to modify and add records.
- In the form, select the second instance of the Supplier ID text box and the Category ID text box, including their labels, and then press DELETE.
InfoPath enables multiple records by using repeating sections (repeating section: A control on a form that contains other controls and that repeats as needed. Users can insert multiple sections when filling out the form.). In this form, users can enter multiple suppliers, as well as multiple products for each supplier. The controls on your form reflect this functionality
To make it easier for users to work with these repeating sections, use the following procedure to add borders and shading, insert titles, and delete any unnecessary space.
- Right-click the second Repeating Section label that appears in the form, and then click Borders and Shading on the shortcut menu.
- On the Borders tab in the Borders and Shading dialog box, click a color in the Color box.
- In the Width box, click 3 pt.
- Under Presets, click Outline, and then click OK.
- Right-click the first Repeating Section label that appears in the form, and then click Borders and Shading on the shortcut menu.
- In the Borders and Shading dialog box, click the Shading tab, and then click a color in the Color box.
- Click OK.
- In the form, type SUPPLIERS in front of the Suppliers repeating section, and then make the text bold.
- Type PRODUCTS in front of the Products repeating section, and then make the text bold.
Your form should now look similar to the following:
When you submit a new record to the Access database by entering data in your form, Access creates the Supplier ID automatically. Because of this, your users won't need to edit or type a new Supplier ID, which means that you can make the Supplier ID text box read-only, which prevents users from trying to modify the value. To make the Supplier ID text box read-only, do the following:
- Double-click the Supplier ID text box.
- In the Text Box Properties dialog box, click the Display tab, and then select the Read-only check box.
You have now finished laying out the data entry view for your form. Now you are ready to work with the query view, in which users can enter values to query the database for existing records.
Lesson 4: Working with the query view
Unlike the data entry view, InfoPath automatically creates the controls and buttons in the query view. In this case, the query view will contain two buttons
First you need to switch to the query view.
- On the View menu, click Manage Views.
- In the Select a view list in the Views task pane, click Query.
The query view should look like this:
|
||
|