I was wondering if I could please ask for some help? I have an infopath form that I have connected to a SQL database (a test database for now). The database contains two tables, the first table has the employee details in (Employee ID [PKey], Fname, Sname, DOB, Job Role) and the second table has details of any appraisals they have had completed (Employee ID [PKey, Appraisal ID, Objective 1, Objective 2 etc]. I have an infopath form which is using a query to help the user complete section one Employee details so that there can be no mistakes (i.e. two employee id numbers), this works fine and I can populate the document with the employees basic details.
The second part of the form is supposed to remain blank so that the worker can complete a new appraisal form but two problems are occuring:
1. Section 2 is querying the second table and pulling all previous appraisal data through
2. Any changes is overwriting this instead of adding a new row of data into the appraisal table despite the appraisal ID and all other information aside from the Employee ID being different.
I have managed to overwrite but I am unable to simply insert a row into table 2, which is what I want to do. Everytime I get it to submit a new row in the appraisal table all of the keys change and a new employee ID is generated.
I'm sorry I'm quite new to SQL databases - our HR system has had an upgrade and now sits on a SQL server so I am having to set the forms up differently. I was new to all of this anyway so to add in that to the mix has me at a bit of a loss
Any advice at all you can give me would be greatly appreciated
P'S - I have a main connection setup and also a secondary "Employee" connection which is simply receiving data from the Employee Table. I have tried Master and Detail controls but this doesn't appear to work the way I need it to, each appraisal needs to be different so cannot show the worker their previous forms.