Database record selection in InfoPath through a dropdown list box

Learn how to select an item from a dropdown list box, retrieve the database record corresponding to the item, and display the data in fields in InfoPath 2003.

ADVERTISEMENTS

Problem

You have an InfoPath form with a drop-down list box that contains names of players. These players are retrieved from a Player table in an Access database. The Player table contains more information about each player, so you would like to automatically populate (fill) fields on the InfoPath form with information about a player when a player is selected from the drop-down list box.

Solution

Apply a filter to a repeating section in InfoPath by using the selected item from a dropdown list box.

Discussion

You can accomplish this functionality as follows:

  1. Create a Player table in a Microsoft Access database (Figure 1).
  2. Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
  3. Add a field called SelectedPlayerID of type Text (string) to the Main data source of the form.
    Your Main data source should now resemble the following figure:
    Main data source of the InfoPath form
    Figure 2. Main data source of the InfoPath form.
  4. Add a table to the InfoPath form.
  5. Drag the SelectedPlayerID field from the Main data source, drop it into the table, and change the control into a Drop-Down List Box.
  6. Drag the Player repeating group from the dataFields node in the Main data source, drop it into the table, and select Repeating Section with Controls from the context menu that appears.
  7. Remove the border from the Repeating Section and disable the possibility to insert and delete sections in its Properties dialog box.
  8. Modify the table and controls to resemble the following figure:
    The InfoPath form in design mode
    Figure 3. The InfoPath form in design mode.
  9. Open the Properties dialog box of the dropdown list box and choose Look up values in the form's data source. Then select the Player node for the Entries field, the @ID node for the Value field, and the @LastName for the Display name field.
  10. Since you want to populate the fields on the InfoPath form with data from only the selected player in the dropdown list, you need to filter the data being shown by the repeating section as follows: Double-click on the repeating section to open its Properties dialog box. Click on the Display tab. Click on the Filter Data... button. Add a Condition that says:
    ID = SelectedPlayerID
  11. To autofill the Main data source of the InfoPath form with data as soon as it is opened, do the following: Go to Tools > Form Options.... Click on the Open and Save tab. Click on the Rules... button. Add an Action that says:
    Query using a data connection: Main connection

You should now be able to select a player from the drop-down list box and retrieve all the information for the selected player in your InfoPath form.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services