Use values from an Access database to populate a list box, drop-down list box, or combo box

In an InfoPath form, you can populate a list box, drop-down list box, or combo box with data from a query data connection to a Microsoft Office Access 2007 (.accdb format) database or an Access database that was saved in an earlier version (.mdb format).

Overview

A list box, drop-down list box, or combo box displays items that users can select when they fill out an InfoPath form. When you design a form template, you can configure these types of controls to display data from a secondary data connection to an Access database.

A secondary data connection is any data connection to an external data source that you add to a form template. You can configure secondary data connections to retrieve data that your users need in order to fill out forms that are based on your form template or to submit form data to an external data source, such as a Web service.

When you add a secondary data connection that queries data to your form template, InfoPath creates a secondary data source that contains data fields and groups that correspond to the way that data is stored in the database. The query data connection retrieves data from the Web service and then stores that data in the form's secondary data source.

You can configure the secondary data connection to save the results of the query on your users' computers so that those users have access to the data even when their computers are not connected to a network. Depending on the nature of the data, you might want to display the query results only when users are connected to a network.

Security Note: If you are using a secondary data connection to retrieve sensitive data from an external data source, you may want to disable this feature to help protect the data from unauthorized use in case the computer is lost or stolen. If you disable this feature, the data will be available only if the user is connected to the network.

If you want to limit the number of items in a list box control, you can use a filter. Use a filter whenever you need to display a subset of the data that is returned by a query that is based on criteria selected by a user. That criteria can be an item that is selected in another control (such as a list box, combo box, drop-down list box, or text box) that is bound to a field in the data source.

Because the data structure in the secondary data source must match the way that data is stored in the database, you cannot modify existing fields or groups in the secondary data source. Find links to more information about data connections and data sources in the See Also section of this article.

Top of Page

Compatibility considerations

You can add a query data connection to an Access database only if you are designing a form template that is not a browser-compatible form template.

Top of Page

Before you begin

Before you can use values from an Access database to populate a control on your form template, you need the following information from your database administrator:

The name and location of the database.

Note: If other users on the network will create forms based on this form template, make sure that your database is accessible to those users.
The name of the table or query that supplies the values for the controls. You will use this table or query as the primary table when you configure the query data connection.
The names of any other tables or queries from which the primary table or query may require data. In most cases, the table relationships are already established in the database. If you need to manually establish the relationships between the primary table or query and another table or query, you will need the related field names of both tables or queries.

Top of Page

Step 1: Add a query data connection

If you do not have an existing query data connection that you can use in your form template, use the following procedure to add a secondary data connection that queries data to your form template. If your form template already has a secondary data connection that you can use, you can skip this section and go to Step 2: Configure the control.

On the Tools menu, click Data Connections.
In the Data Connections dialog box, click Add.
In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.
On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
On the next page of the wizard, click Select Database.
In the Select Data Source dialog box, browse to the location of your database.

Note: If your database is stored in a network location, browse to the universal naming convention (UNC) path of the location. Do not browse to the network location through a mapped network drive. If you use a mapped network drive (that is, assigning a letter to a network drive, such as H: or Z:), forms that users create that are based on this form template will search for the database on that specific mapped network drive. If the user does not have network drive mapped in exactly the same way, the form will not find the database.
Click the name of your database, and then click Open.
In the Select Table dialog box, click the primary table or query that you want to use, and then click OK.
On the next page of the wizard, select the Show table columns check box.

By default, all the fields in the table or query are added to the main data source of the form template.
Clear the check boxes for the fields that you do not want to include in the main data source.
Add any additional tables or queries that you want to include in the query data connection.

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.
To make the results of the query data connection available when the form is not connected to a network, select the Store a copy of the data in the form template check box.

Security Note: Selecting this check box stores the data on the user's computer when the form uses this data connection. If the form is retrieving sensitive data from this data connection, you might want to disable this feature to help protect the data in case the computer is lost or stolen or is otherwise accessed by an unauthorized user.
Click Next.
On the next page of the wizard, type a descriptive name for this secondary data connection, and then verify that the information in the Summary section is correct.
To configure the form to automatically receive data when it opens, select the Automatically retrieve data when form is opened check box.

Top of Page

Step 2: Configure the control

If the form template has multiple views, click View name on the View menu to go to the view with the control where you want to display the data from the secondary data source.
Double-click the list box, drop-down list box, or combo box control on your form template that you want to configure.
Click the Data tab.
Under List box entries, click Look up values from an external data source.
In the Data source list, click the data source that you want to use.
Click Select XPath , next to the Entries box.
In the Select a Field or Group dialog box, specify the field or group whose fields contain the data that will be shown to the user and the data that will be submitted to the external data source by doing one of the following.

Specify that the data that the user will see is the same data that the user will submit
- Click a field, and then click OK.
Specify that the data that the user will see is different from the data that the user will submit

Note: In this scenario, the data that the user will see comes from one of the fields in the group, and the data that the user will submit is contained in another field in the same group.
1. Click a group, and then click OK.
2. In the Control Properties dialog box, click Select XPath , next to the Value box.
3. In the Select a Field or Group dialog box, click a field that contains the data that the user will submit to the external data source, and then click OK.
4. In the Control Properties dialog box, click Select XPath , next to the Display name box.
5. In the Select a Field or Group dialog box, click a field that contains the data that will appear in the control, and then click OK.
If the field in the Display name box has values with similar display names, and you want to display only unique names, select the Show only entries with unique display names check box.

Top of Page

Optional: Set a filter to limit the items in the control

Double-click the list box, drop-down list box, or combo box control on your form template that you want to configure.
Click the Data tab.
Click Select XPath next to the Entries box.
In the Select a Field or Group dialog box, click the field or group that contains the fields that provide the values for the control, and then click Filter Data.
In the Filter Data dialog box, click Add.

Note: If you want to add a condition to an existing filter, click the filter that you want to apply, and then click Modify.
In the first box in the Specify Filter Conditions dialog box, click the name of the field whose data you want to filter.
In the second box, click the type of filter that you want to use.
In the third box, click the type of condition that you want to apply to the filter, and then type the condition.
To configure more than one condition for the filter, click And, and then do one of the following:
- To apply the existing condition and the new condition to your filter, click and, and then add your new condition.
- To apply either the existing condition or the new condition to your filter, click or, and then add the additional condition.
Click OK to close the dialog boxes.
To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

The form template opens in a new window.
In the preview window, select various values in the filter control to test that the filter is working correctly.

The filter is not working correctly

If the values in the control that contains a filter are not correct, either because there are too many values or not enough values, try the following:
- If you are using multiple conditions in your filter, remove all but one condition in order to check that the correct values are returned from that single condition. If the first condition returns the correct values, then add another condition, and test it.
- Filters make a distinction between uppercase and lowercase characters. If you want to display data that begins with an uppercase or lowercase character, create a filter with an uppercase letter condition, select or in the Specify Filter Condition dialog box, and then create a lowercase letter condition.
- If the filtered values are exactly opposite of what you expect, for example the products from all of the suppliers display instead of the products from a single supplier, change the filter type in the condition. For example, if your condition uses the filter type is equal to and you are getting too many products, try using the filter type is not equal to.

Top of Page

Microsoft Office Tutorials

Thursday, March 9, 2017