Query an Access database table in InfoPath by a list of numbers

Applies to: InfoPath 2003

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.

ADVERTISEMENTS

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:

 

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.

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