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
Solved

Infopath VBA to submit to poptional databases on Submit_Clicked

Posted on 2014-01-16
12
482 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
  • 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 35

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 35

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 500 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 35

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

791 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