Query an Access database table in InfoPath by a list of numbers
Use JScript to perform a search to find only those records in a Microsoft Access table that correspond to a given comma-separated list of numbers.
You have a Microsoft Access database table that has a number field. You would like to be able to type in a list of numbers like e.g. 24,10,36 and be able to find the records corresponding to these numbers.
Use JScript code to modify the SQL Command string of the Main data connection of the form to contain a WHERE-clause with OR statements specifying the numbers to search on.
You can accomplish this functionality as follows:
- Create a Player table in a Microsoft Access database (Figure 1). The ID field of this table is set to be the primary key of the table and has type AutoNumber. We will be querying on the No field.
- Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
You can see the SQL statement that InfoPath created for you by going to Tools > Data Connections..., selecting Main connection, clicking on the Modify... button, and then on the Edit SQL... button on the Data Connection Wizard. InfoPath created the following SQL statement for the Main data connection:
select [ID],[FirstName],[LastName],[No],[Position] from [Player] as [Player]
- Open the Data Source pane and add a Field (element) with the name NumbersToQuery and data type Text (string) under myFields.
Your Main data source should now resemble the following figure:
Figure 2. Main data source of the InfoPath form.
- Drag the NumbersToQuery field from the Data Source pane and drop it into the table on the form.
- Drag the Player repeating group under the dataFields group from the Data Source pane and drop it into the table as a Repeating Table.
- Add a Button control under the Repeating Table and give it the label Query by Numbers.
- Delete the other button controls that InfoPath added to the form after you created the form.
- Modify the table and controls to resemble the following figure:
Figure 3. The InfoPath form in design mode.
- Make sure that
your form is set to use JScript as the Default programming language by setting this through
Tools > Options... and the Design tab before you add the event handler
for the button. After doing this you can double-click on the Query by Numbers button to open its Properties dialog
box, click on the Edit Form Code... code button to open Microsoft Script Editor
and add the following code in the OnClick event handler for the button:
// Retrieve the SQL Command string of the Main data adapter
var origCommand = XDocument.DataAdapters["Main connection"].Command;
// Retrieve the list of numbers to query; this list is assumed
// to be comma-separated
var numbersToQuery = XDocument.DOM.selectSingleNode("/dfs:myFields/my:NumbersToQuery").text;
if (numbersToQuery != "")
var i = 0;
var buffer = "";
// Parse the list of numbers and put them in an array
var arrNumbers = numbersToQuery.split(",");
// Loop through all the numbers and construct a WHERE-clause
// for the SQL Command string containing OR statements
for (i=0; i<arrNumbers.length; i++)
var value = parseInt(arrNumbers[i]);
if (buffer != "")
buffer += " OR ";
buffer += "[Player]![No] = " + value;
// Construct the new SQL Command string with WHERE-clause
var sqlCommand = origCommand;
if (buffer != "")
sqlCommand += " WHERE " + buffer
// Set SQL Command string for the Main data adapter to the newly
// constructed SQL Command string
XDocument.DataAdapters["Main connection"].Command = sqlCommand;
// Query the Main data source
// Restore the SQL Command string of the Main data adapter
XDocument.DataAdapters["Main connection"].Command = origCommand;
You should now be able to type in a comma-separated list of numbers like e.g. 24,10,36 and be able to perform a search to find only those records corresponding to the numbers specified.
Related InfoPath Articles:
- Database record selection in InfoPath through a dropdown list box
- Sequential database table record navigation
- Using a Receive data connection in InfoPath to execute an SQL Server stored procedure
- Display Access 2010 data in an InfoPath 2010 browser form
- Use an InfoPath form to perform CRUD actions on an Access table with Memo field
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. 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.