Design a form template based on a Microsoft SQL Server database

You can design a form template that can work with a Microsoft SQL Server database to either query data or query and submit data.

Overview

A form can have one primary data connection, called the main data connection, and it can optionally have one or more secondary data connections. Depending on your goals for the form, a data connection might query or submit form data to an external data source, such as a Microsoft SQL Server database or a Web service.

When you design a form template that is based on a database, Microsoft Office InfoPath creates a main data source with groups that contain query fields and data fields and a query data connection as the main data connection for the form template. These fields and groups correspond to the way that data is stored in the tables in the database. Query fields contain the data that is entered by a user to limit the query results to records that match the data in the query fields. When a form based on this form template uses the main data connection, InfoPath creates a query by using the data in the query fields. InfoPath then sends the query through the query data connection. The database returns the results of the query back to the form through the query data connection. The results of the query are put into data fields, which can be edited through controls that are bound to these fields.

Because the data structure of the query and the data fields must match the way that data is stored in the database, you cannot modify these fields or groups in the main data source. You can only add fields or groups to the root group in the main data source. Find links to more information about data sources in the See Also section.

A form can submit data to a database through the form's main data connection if the form template that the form is based on and the database meet the following requirements:

The form template is not a browser-compatible form template InfoPath will not create a submit data connection in the main data connection if you are designing a browser-compatible form template. To allow users to submit data in a form that is based on a browser-compatible form template, use a Web service that works with the database.
The left table in each pair of related tables in the main data source contains a primary key At least one of the relationships for every pair of related tables must include a primary key from the left table.
None of the data fields in the main data source of the form store a large binary data type InfoPath will disable the submit data connection if the query includes fields that can store a large binary data type, such as pictures, images, OLE objects, file attachments, the Office Access memo data type, or the SQL Text data type.

When InfoPath enables the submit data connection, users are allowed to submit the data in the data fields in the main data source to the database. If InfoPath enables the submit data connection, you can customize the submit options for the forms that are based on this form template.

Top of Page

Compatibility considerations

When you design a form template that is based on a database, you have the option of designing a browser-compatible form template. InfoPath will create a query data connection as the main data connection in a browser-compatible form template. However, browser-compatible form templates cannot be configured to allow users to submit data to a database. Therefore, if you are designing a form template based on a SQL Server database and you want your users to submit their form data to the database through the main data connection, you cannot make that form template browser-compatible.

Top of Page

Before you begin

In order to design your form template based on a SQL Server database, you need the following information from your database administrator:

The name of the server that contains the database that you will connect your form template to.
The name of the database that you will use with this form template.
The authentication required by the database. The database can use either Microsoft Windows authentication or SQL Server authentication to determine how users can access the database.
The name of the table that contains the data that you want to send to the form or that will receive data from the form. This is the primary table. If you are going to use more than one table in the database, you need the names of those other, child tables. You also need the names of the fields in the child tables that have relationships with the fields in the primary table.

Top of Page

Design the form template

To design a form template with a query data connection, you need to do the following:

Create the form template When you create a form template that is based on a database, InfoPath creates a query data connection as the main data connection between the form template and the database. This process automatically creates the form template's main data source.
Add one or more controls to display the query results To allow users to see and edit the data in the fields in the main data source when they open the form, you can add a control to the form template and then bind that control to a field in the main data source.

Step 1: Create the form template

On the File menu, click Design a Form Template.
Under Design a new, in the Design a Form Template dialog box, click Form template.
In the Based on list, click Database.
If you are designing a browser-compatible form template, select the Enable browser-compatible features only check box.

Note: The Data Connection Wizard will not enable the submit data connection in a browser-compatible form template. To allow users to submit their data to a database from forms that are based on a browser-compatible form template, add a secondary data connection to a Web service that works with the database. Find links to more information about secondary data connections in the See Also section.
Click OK.
In the Data Connection Wizard, click Select Database.
In the Select Data Source dialog box, click New Source.
In the What kind of data source do you want to connect to list, click Microsoft SQL Server, and then click Next.
In the Server name box, type the name of the server with the SQL Server database.
Under Log on credentials, do one of the following:
- If the database determines who has access based on the credentials used in a Microsoft Windows network, click Use Windows Authentication.
- If the database determines who has access based on a specified user name and password that you get from the database administrator, click Use the following User Name and Password, and then type your user name and password in the User Name and Password boxes.
Click Next.
In the Select the database that contains the data you want list, click the name of the database that you want to use, select the Connect to a specific table check box, click the name of the primary table, and then click Next.
On the next page of the wizard, type a name for the file that stores the data connection information in the File Name box, and then click Finish to save these settings.

If you plan to use other tables in the query data connection, you can add the other tables in this page of the wizard.

How?
1. Click Add Table.
2. In the Add Table or Query dialog box, click the name of the child table, and then click Next. InfoPath attempts to set the relationships by matching field names in both tables. If you do not want to use the suggested relationship, select the relationship, and then click Remove Relationship. To add a relationship, click Add Relationship. In the Add Relationship dialog box, click the name of each related field in the respective column, and then click OK.
3. Click Finish.
4. To add additional child tables, repeat these steps.
Click Next.
On the last page of the wizard, type a name for the main data connection. This name will appear in the Data source list in the Data Source task pane.
If your form template meets the requirements in the Overview section, this page of the wizard will indicate that InfoPath enabled the submit data connection in the main data connection. To change the name for the submit data connection, type a new name in the appropriate box. If you want your users to submit their form data through another submit data connection that you will add to the form template later, click to clear the Enable submit for this connection check box.

Note: If your form template does not meet the requirements in the Overview section, InfoPath disables the submit data connection, and the Enter a name for the submit connection box and the Enable submit for this connection check box are unavailable (disabled). If InfoPath disables the submit data connection, the main data connection for your form template will have only a query data connection.

Step 2: Add one or more controls to display the query results

If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
Drag a control onto your form template.
In the Control Binding dialog box, select the group or field that you want to bind the control to.

Top of Page

Configure the submit options

If your form template and the tables that you selected in the Data Connection Wizard meet the requirements in the Overview section, InfoPath configures your form template to submit data through its main data connection. If you choose to use this submit data connection, InfoPath configures the form template so that users can submit their form data to the database and adds both the Submit button to the Standard toolbar and the Submit command to the File menu when users fill out the form. InfoPath also configures the form template so that, when users submit their forms, the form remains open and a message is displayed to the user that indicates whether the form was successfully submitted. You can change the text on the Submit button and the behavior after the user submits a form.

On the Tools menu, click Submit Options.
1. To change the name of the Submit button that appears on the Standard toolbar and the Submit command that appears on the File menu when users fill out the form, type the new name in the Caption box in the Submit Options dialog box.
  
  Tip: If you want to assign a keyboard shortcut to this button and command, type an ampersand (&) before the character that you want to use as a keyboard shortcut. For example, to assign ALT+B as the keyboard shortcut for the Submit button and command, type Su&bmit.
If you do not want people to use a Submit command or the Submit button on the Standard toolbar when they fill out your form, clear the Show the Submit menu item and the Submit toolbar button check box.
1. By default, after users submit a form, InfoPath keeps the form open and displays a message to indicate if the form was successfully submitted. To change this default behavior, click Advanced, and then do one of the following:
  - To close the form or create a new blank form after the user submits a completed form, click the option that you want in the After submit list.
  - To create a custom message to indicate if the form was successfully submitted, select the Use custom messages check box, and then type your messages in the On success and On failure boxes.
    
    Tip: Use a message in the On failure box to tell users what to do if they cannot submit their form. For example, you can suggest that users save their form and contact someone for further instructions.
  - If you do not want to display a message after the user submits a form, clear the Show success and failure messages check box.

Top of Page

Microsoft Office Tutorials

Sunday, October 21, 2018