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.
You have a database table to which you would like to add records through code.
Use VBScript to add nodes to a repeating group and submit the changes to the database.
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.
- Create a new InfoPath form: Use New from Data Connection... and bind it to the Player table.
- 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.
- 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 under the Repeating Table and give it the label Add Player.
- Modify the table and controls to resemble the following figure:
Figure 3. The InfoPath form in design mode (default view).
- Add a second view to the form and rename it to AddRecordView.
- Add a rule to the Add Player button that says:
Switch to view: AddRecordView
- 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
- 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.
- Modify the view to resemble the following figure:
Figure 4. The InfoPath form in design mode (AddNewRecord view).
- Add a Rule with the following Actions to the Cancel button:
- 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:
' 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
' Submit the form to add the new record
' Switch back to the default view
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
You should now be able to add new records to the database table through VBScript code.
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.
Related InfoPath Articles:
- Use an InfoPath form to perform CRUD actions on an Access table with Memo field
- Bulk import InfoPath forms into an Access 2013 database
- 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