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.

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:
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
13. 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.

Related InfoPath Articles:

• Database record selection in InfoPath through a dropdown list box
• Query an Access database table in InfoPath by a list of numbers
• Using a Receive data connection in InfoPath to execute an SQL Server stored procedure