Programmatically add records to a database table using VBScript

Use VBScript to programmatically add records to a database table that has been bound to an InfoPath form.

ADVERTISEMENTS

Problem

You have a database table to which you would like to add records through code.

Solution

Use VBScript to add nodes to a repeating group and submit the changes to the database.

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.
  2. Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
  3. Open the Data Source pane and add a group with the name newRecord under myFields, and text field elements with the names firstName, lastName, no, and position under the newRecord group.
    Your Main data source should now resemble the following figure:

    Figure 2. Main data source of the InfoPath form.
  4. Drag the Player repeating group under the dataFields group from the Data Source pane and drop it into the table as a Repeating Table.
  5. Add a Button under the Repeating Table and give it the label Add Player.
  6. Modify the table and controls to resemble the following figure:

    Figure 3. The InfoPath form in design mode (default view).
  7. Add a second view to the form and rename it to AddRecordView.
  8. Add a rule to the Add Player button that says:
    Switch to view: AddRecordView
  9. Go to Tools > Form Options..., click on the Open and Save tab, and click on the Rules... button to add a rule that says:
    Query using a data connection: Main connection
  10. Switch to the AddNewRecordView, drag the fields from under the newRecord node and drop them onto the view, and add two buttons Save and Cancel.
  11. Modify the view to resemble the following figure:

    Figure 4. The InfoPath form in design mode (AddNewRecord view).
  12. Add a Rule with the following Actions to the Cancel button:
  13. Set a field's value: firstName = ""
    Set a field's value: lastName = ""
    Set a field's value: no = ""
    Set a field's value: position = ""
    Switch to view: View 1
  14. Make sure that your form is set to use VBScript 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 Save 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 to the OnClick event handler for the button:
    Dim objFieldsDataNode
    Dim objPlayerNode
    Dim objNewPlayerNode
    Dim strNewPlayerFirstName
    Dim strNewPlayerLastName
    Dim strNewPlayerNumber
    Dim strNewPlayerPosition

    ' Retrieve the dataFields node
    Set objFieldsDataNode = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields")

    ' Retrieve the first Player node under the dataFields node and clone it to create a new Player node
    Set objPlayerNode = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Player")
    Set objNewPlayerNode = objPlayerNode.cloneNode(True)

    ' Retrieve the values for the new player entered by the user
    strNewPlayerFirstName = XDocument.DOM.selectSingleNode("/dfs:myFields/my:newRecord/my:firstName").text
    strNewPlayerLastName = XDocument.DOM.selectSingleNode("/dfs:myFields/my:newRecord/my:lastName").text
    strNewPlayerNumber = XDocument.DOM.selectSingleNode("/dfs:myFields/my:newRecord/my:no").text
    strNewPlayerPosition = XDocument.DOM.selectSingleNode("/dfs:myFields/my:newRecord/my:position").text

    ' Set the attributes for the new Player node; the ID attribute will be automatically created
    ' by the database, so we need not set objNewPlayerNode.attributes(0).text
    objNewPlayerNode.attributes(1).text = strNewPlayerFirstName
    objNewPlayerNode.attributes(2).text = strNewPlayerLastName
    objNewPlayerNode.attributes(3).text = strNewPlayerNumber
    objNewPlayerNode.attributes(4).text = strNewPlayerPosition

    ' Add the new Player node to the dataFields node
    Call objFieldsDataNode.appendChild(objNewPlayerNode)

    ' Submit the form to add the new record
    XDocument.Submit

    ' Switch back to the default view
    XDocument.View.SwitchView("View 1")

You should now be able to add new records to the database table through VBScript code.

 
 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