?
Solved

Infopath VBA to submit to poptional databases on Submit_Clicked

Posted on 2014-01-16
12
Medium Priority
?
492 Views
Last Modified: 2014-03-20
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 current code that inserts into both tables based on_click of Submit button, but errors out when optional section is not selected:

 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()
                cmd_tp.Parameters.AddWithValue("lab_num_tp", lab_num_tp)
                cmd_tp.Parameters.AddWithValue("part_num_tp", part_num_tp)
                cmd_tp.Parameters.AddWithValue("part_name_tp", part_name_tp)
                cmd_tp.Parameters.AddWithValue("supplier_tp", supplier_tp)
                cmd_tp.Parameters.AddWithValue("prod_code_tp", prod_code_tp)

                Try
                    conn.Open()
                    cmd_tp.ExecuteNonQuery()

                Catch ex As Exception
                    MessageBox.Show(ex.Message, "An error occurred", MessageBoxButtons.OK, MessageBoxIcon.Error)

                Finally
                    conn.Close()

                End Try
            Else
                If part_num_tp.Length = 0 Then
                    part_num_tp = ""

                End If
            End If

        End Sub

Open in new window

0
Comment
Question by:jaspence
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 39788786
This is basically a guess but I suspect that you need to check that:"/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts is not null before you check that "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts/@part_num_tp is null in line 36 of the posted code.
0
 

Author Comment

by:jaspence
ID: 39789378
What code would I have to insert to test that?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39789418
You have defined a bunch of variables as strings and in the Dim statements are setting them to a value.  If any of these controls are null, you will get an error because you can't set a string to null.  Either change the definition from String to Variant or add code to verify that each field is not null before trying to copy it to a string.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:jaspence
ID: 39789498
What code would I have to insert to change that. I tried changing the string to variant and it autocorrected to Object. After I run the debugging using Object and not String I still get the same error on the same line.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39789715
It wouldn't let you change
Dim lab_num As String

to

Dim lab_num As Variant?
0
 
LVL 15

Accepted Solution

by:
JimFive earned 2000 total points
ID: 39789722
Before line 36 add:
            If Not Convert.IsDBNull(mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts", NamespaceManager)) Then

Open in new window


And then add another End If at line 50.

Also, why do you have an Else with no statements at line 48?
0
 

Author Comment

by:jaspence
ID: 39789724
Nope it auto changes variant to object:

Dim lab_num_tp As Object = ""
0
 

Author Comment

by:jaspence
ID: 39789772
Added before line 36:
If Not Convert.IsDBNull(mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/d:tested_parts", NamespaceManager)) Then 

Open in new window

and added End If after line 50

Still get the same error on the same line 39.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39789819
Try separating the Dim from the rest of the statement.
Dim lab_num As Variant
lab_num = mainDS.SelectSingleNode( _
              "/dfs:myFields/dfs:dataFields/d:lab_log/@lab_num", NamespaceManager).Value

Open in new window

0
 

Author Comment

by:jaspence
ID: 39794150
I tried separating the Dim from the rest of the statement as suggested. The "Variant" automatically changed to "Object" again. I was able to submit using this code, but still receiving the error at the optional section at line 39.
To reference a previous response:
"Either change the definition from String to Variant or add code to verify that each field is not null before trying to copy it to a string."
Since I cant change to variant (auto corrects to Object) how can I verify that each field is not null before trying to copy it to a string?
0
 
LVL 6

Expert Comment

by:livanescu
ID: 39935478
wouldn't it be simple to disable in InfoPath UI the Submit button if one section is missing and display instead in UI a message like: "please add missing data for 'tested_parts' ..." ?
this way you validate before submission...
just a thought...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question