Re: Infopath Optional Section Submit With VBA

jaspence — Mon, 20 Jan 2014 17:41:42 GMT

Worked like a champ. Thanks for your help!

Re: Infopath Optional Section Submit With VBA

Jimmy — Mon, 20 Jan 2014 15:55:14 GMT

You seem to be using Convert.IsDbNull to check whether an XPath selection returns nothing, but SelectSingleNode returns Nothing, not DbNull, when no matching node is found. Perhaps you should try the following:

If mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager) IsNot Nothing Then
.....

Re: Infopath Optional Section Submit With VBA

jaspence — Fri, 17 Jan 2014 16:48:46 GMT

part_num_tp = mainDS.SelectSingleNode( _
"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp",

Re: Infopath Optional Section Submit With VBA

Jimmy — Fri, 17 Jan 2014 06:12:39 GMT

Have you walked through the code in a debugger? On which line is the error occurring?

Infopath Optional Section Submit With VBA

jaspence — Thu, 16 Jan 2014 18:09:13 GMT

I have two MS Access tables that comprise the Infopath Main Connection datasource. The lab_log table is the parent table and all data is inserted into that table. The second table tested_parts is related to the lab_log table by lab_num and is an optional section on the infopath form. Data is entered in both sections on the form and the VBA code inserts correctly into both tables when the submit button is clicked. When I only enter into the lab_log section and do not activate the tested_parts optional section I get the error:

"NullReferenceException was unhandled by user code, Object reference not set to an instance of an object. A first chance exception of type 'System.NullReferenceException' occurred."

Sample of my code that goes into both tables based on_click of Submit button:

Public Sub Submit_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

Dim mainDS As XPath.XPathNavigator = MainDataSource.CreateNavigator()

Dim lab_num As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@lab_num", NamespaceManager).Value

Dim type As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@type", NamespaceManager).Value

Dim part_num As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@part_num", NamespaceManager).Value

Dim part_name As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@part_name", NamespaceManager).Value

Dim prod_code As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@prod_code", NamespaceManager).Value

Dim entry_date As DateTime = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@entry_date", NamespaceManager).Value

Dim due_date As DateTime = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@due_date", NamespaceManager).Value

Dim schedule As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@schedule", NamespaceManager).Value

Dim supplier As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@supplier", NamespaceManager).Value

Dim who As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@who", NamespaceManager).Value

Dim requestor As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@requestor", NamespaceManager).Value

Dim comment As String = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/@comment", NamespaceManager).Value

'Tested Parts Section

Dim part_num_tp As String = ""

Dim part_name_tp As String = ""

Dim lab_num_tp As String = ""

Dim supplier_tp As String = ""

Dim prod_code_tp As String = ""

If Not Convert.IsDBNull(mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager)) Then

part_num_tp = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp", NamespaceManager).Value

part_name_tp = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_name_tp", NamespaceManager).Value

lab_num_tp = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@lab_num_tp", NamespaceManager).Value

supplier_tp = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@supplier_tp", NamespaceManager).Value

prod_code_tp = mainDS.SelectSingleNode( _

"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@prod_code_tp", NamespaceManager).Value

Else

End If

Dim conn As New OleDbConnection( _

"Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=U:\MCL\datasources\newweb_log.accdb;")

Dim cmd As OleDbCommand = conn.CreateCommand()

cmd.CommandType = System.Data.CommandType.Text

Dim sb As New System.Text.StringBuilder()

sb.Append(

"INSERT INTO lab_log ")

sb.Append(

"([lab_num], [part_num], [part_name], [entry_date], [due_date], [schedule], [supplier], [who], [requestor], [comment]) ")

sb.Append(

"VALUES ")

sb.Append(

"(@lab_num, @part_num, @part_name, @entry_date, @due_date, @schedule, @supplier, @who, @requestor, @comment)")

cmd.CommandText = sb.ToString()

cmd.Parameters.AddWithValue(

"lab_num", lab_num)

cmd.Parameters.AddWithValue(

"part_num", part_num)

cmd.Parameters.AddWithValue(

"part_name", part_name)

cmd.Parameters.AddWithValue(

"entry_date", entry_date)

cmd.Parameters.AddWithValue(

"due_date", due_date)

cmd.Parameters.AddWithValue(

"schedule", schedule)

cmd.Parameters.AddWithValue(

"supplier", supplier)

cmd.Parameters.AddWithValue(

"who", who)

cmd.Parameters.AddWithValue(

"requestor", requestor)

cmd.Parameters.AddWithValue(

"comment", comment)

Try

conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message,

"An error occurred", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

conn.Close()

End Try

If part_num_tp.Length > 0 Then

Dim cmd_tp As OleDbCommand = conn.CreateCommand()

cmd_tp.CommandType = System.Data.CommandType.Text

sb =

New System.Text.StringBuilder()

sb.Append(

"INSERT INTO tested_parts ")

sb.Append(

"([lab_num_tp], [part_num_tp], [part_name_tp], [supplier_tp], [prod_code_tp]) ")

sb.Append(

"VALUES ")

sb.Append(

"(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp, @prod_code_tp)")

cmd_tp.CommandText = sb.ToString()