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.
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.
Apply a filter to a repeating section in InfoPath by using the selected item from a dropdown list box.
You can accomplish this functionality as follows:
- Create a Player table in a Microsoft Access database (Figure 1).
- Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
- 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:
Figure 2. Main data source of the InfoPath form.
- Add a table to the InfoPath form.
- Drag the SelectedPlayerID field from the Main data source, drop it into the table, and change the control into a Drop-Down List Box.
- 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.
- Remove the border from the Repeating Section and disable the possibility to insert and delete sections in its Properties dialog box.
- Modify the table and controls to resemble the following figure:
Figure 3. The InfoPath form in design mode.
- 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.
- 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
- 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.
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.
Related InfoPath Articles:
- Auto-fill a repeating table with data from a SharePoint list when a row is inserted
- Master/detail across views
- Auto-populate a drop-down list box with past and future dates
- Cascading drop-down list boxes in InfoPath 2010 - the non-traditional way
- Auto-populate InfoPath repeating table row on insert