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.

Problem

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.

Solution

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.

Discussion

You can accomplish this functionality as follows:

1. 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.
2. 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]
   You will be modifying this SQL statement later through code by appending a WHERE-clause to it to be able to query on a given list of values for the No database field.
3. 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.
4. Drag the NumbersToQuery field from the Data Source pane and drop it into the table on the form.
5. Drag the Player repeating group under the dataFields group from the Data Source pane and drop it into the table as a Repeating Table.
6. Add a Button control under the Repeating Table and give it the label Query by Numbers.
7. Delete the other button controls that InfoPath added to the form after you created the form.
8. Modify the table and controls to resemble the following figure:
   
   Figure 3. The InfoPath form in design mode.
9. 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 (!isNaN(value))
{
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
XDocument.DataAdapters["Main connection"].Query();

// 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