Background:
I have two database tables, one is lab_entry and the second is tested_parts. Every job is submitted to the lab_entry database. I have an optional section in my form that submits to a tested_parts database that we can select when an annual test is performed.
I am trying to create a submit button in infopath that queries a main connection database based on two entered fields in the form (i.e. part number and supplier). I want this query to identify the index (auto-numbered) for the last time the part number/supplier combination was tested and update the records date_tested to now (). So basically just updating the record if that part number/supplier combination has already been tested. For when a new part number/supplier combination comes in I would also still need to be able to create a new record. I have worked with using the Rules and Conditions of the Submit button but just cant seem to get anything to just update the previous record. Every time the submit button is clicked a new record is created in both tables. Another way to look at this is I am simply trying to query the tested_parts table where part_number(form)=part_number(tested_parts DB) and supplier(form)=supplier(tested_parts DB) and update that record in the tested parts table.