InfoPath Dev

I have a form that submits to a database connection and then to SharePoint, which takes the primary key with it.  If I click on the document to open it, it will not allow me to make changes because it gives the error

InfoPath cannot submit the form.An error occurred while the form was being submitted.
Row cannot be located for updating. Some values may have been changed since it was last read.

I can move the submit to SharePoint first and then to the database and the id doesn't go to SharePoint; however when you edit the document and resubmit to the database, a new line is added to the database instead of updating the original line because the primary id is not in the form.

I found some information about setting a rule on the submit to check the id but I can't figure out how and where I should place the rule.  Obviously I need the primary key go with the form to SharePoint.  I was actually trying to get the SharePoint name to be the added date and the id field so that when the form was updated the name didn't change.  I've tried several different ways to do this but I continuously get the message above.

Does anyone know how I can resolve this problem?  I'm supposed to have a good update on this project tomorrow so any help today would be greatly appreciated.

Thanks

siboyd

My first question would be, is the form created from a datasource?

Sounds like your form submits to a database on open,  if you are later submitting, I would not bother with this step.  Another possible reason is that you have an old document on sharepoint and since that document was saved to sharepoint, you have published a new form to that form library.

 In order to submit to a database you have to make sure that atleast one field is changed.  How is your id create?  From a database or a concat of some fields  on your form?   The reason why it creates a new line in the database is because something is changing in your id field.

 As for your rule, do it checking on the button.  When you have your button to submit the form, click rules.  In there you can add actions based on certain stipulations just like any text box on the form.  So you would check if id is blank, and if it is blank then don't submit, show a message.  Also make sure you are setting your id on your initial submit to sharepoint BEFORE you submit it to the database and sharepoint.  The submit to database and sharepoint I usually always put at the end of my actions.

Barmatt80 has good points.

 I am thinking you may be clearing the ID field during the submit so that when it is reopened it is blank?  Typically when you opened again you would requery your database data which would update the existing ID again anyway.

The primary key is an automatic generated number in the database and that primary key is related throughout the child tables.  I wanted it to submit to a database first because i want to use lookup tables (created with views) to pull in some last submitted default information.  I have actually disabled all of those trying to resolve this problem but that's the primary reason I want it to go to the database first.  Another edit team will pull up each submitted document and make corrections and resubmit those changes back to the database but it could be several days after the initial information is submitted to the database and that data needs to be available as soon as the forms are submitted.

The SharePoint name is generated by a concatenation of fields from the form which can change depending on what changes are made within the form but if the primary key is in the form, it shouldn't matter how many different SharePoint names you have... correct?  I was actually trying to resolve the changine SharePoint names when I discovered what was happening with the form.  I had a date field set to now(), converted it to a string and unchecked the box to recalculate,  and concatenated it with the Primary key id.  This way it wouldn't change.

Changes are being made to the form when I pull it up, but nothing that changes the primary ID. 

I'm not sure if I understand what you're suggesting in the last paragraph.   When you are speaking about Id are you referring to the primary id in the database table or the filename that is given to SharePoint. I need the information to go to the database first, then to SharePoint, edited and resubmitted back to the same line with the primary key in the database.  If I submit to SharePoint first, it won't have the primary key so what do you mean by "Also make sure you are setting your id on your initial submit to sharepoint BEFORE you submit it to the database and sharepoint". 

SharePoint .... Database.... SharePoint ????

I have made sure I republished the form each time I've made changes to make sure that the new form being added is with the lates template. 

The primary Id is blank if it goes to SharePoint first, then to the database (but I get two records). 

These are the submit rules:

Set a fields value : submitted = "Y"

Set a fields value : @Addedtotable = now()

Submit to Main Connection

Submit to SharePoint Library.

1.  The primary key field is on the form and goes with the form to SharePoint but won't submit and gives the message. 

2.  The SharePoint name is not generated by either of the first two. 

 "Still confused"  

If a submit to the database works without the SharePoint then something in the SharePoint submit is goofing things up.  Typically though I do not submit to SharePoint as well as to the database since the data in the form can get out of date from the Database information.  Typically you just open a new document each time and view the latest information.

Do you requery your data when a form is reopened?  If not then the form would not overwrite the exisitng data or a change in the data would prevent an update.

The issue may be with how you have this process architected.