Infopath VBA UPDATE not working properly

jaspence — Fri, 24 Jan 2014 14:55:06 GMT

I have two main tables that I am entering data into and need to be able to update a record set if one exists for a part number/supplier combination. I have the code working to correctly populate the tested_parts table when a record does not already exist, but can not get the tested_parts table to update when a record does exist. I have a rule, within my Infopath form, which does the query of the tested_parts table based on entered data (supplier_tp and part_num_tp combination) and identifies the record set with the primary key index_tp. If a record set exists I need to update the tested_parts table with lab_num_tp, alloy_spec, last_tested fields associated with the index_tp identified. I have run the debugger and the correct values are being obtained based on the entered data and rules query so I am assuming there is something wrong with my update code and am to much of a noob to catch it.

If part_num_tp.Length > 0 Then

'If activated

If index_tp.Length > 0 Then

'If previous record exists

Dim cmd_tp As OleDbCommand = conn.CreateCommand()

cmd_tp.CommandType = System.Data.CommandType.Text

sb =

New System.Text.StringBuilder()

sb.Append(

"UPDATE tested_parts ")

sb.Append(

"SET [lab_num_tp] = lab_num_tp, [alloy_spec] = alloy_spec, [last_tested] = last_tested ")

sb.Append(

"WHERE [index_tp] = ")

sb.Append(index_tp)

cmd_tp.CommandText = sb.ToString()

cmd_tp.Parameters.AddWithValue(

"lab_num_tp", lab_num_tp)

cmd_tp.Parameters.AddWithValue(

"alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"index_tp", index_tp)

Else

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], [alloy_spec], [last_tested], [DFAR]) ")

sb.Append(

"VALUES ")

sb.Append(

"(@lab_num_tp, @part_num_tp, @part_name_tp, @supplier_tp, @prod_code_tp, @alloy_spec, @last_tested, @DFAR)")

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)

cmd_tp.Parameters.AddWithValue(

"alloy_spec", alloy_spec)

cmd_tp.Parameters.AddWithValue(

"last_tested", last_tested)

cmd_tp.Parameters.AddWithValue(

"DFAR", DFAR)

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

End If

Microsoft Access

Infopath VBA UPDATE not working properly