• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 762
  • Last Modified:

Infopath 2003 with SQL Server 2000 DB - Need to update single table in Main Datasource or make secondary data source repeating

Hi,
Is there any way in Infopath 2003 to have a secondary data source act as a repeating control? Or allow submission to only one table in the main data source

Below is what I am trying to accomplish:

1. I have and infopath form that submits to an SQL Database - This works great, there are 5 tables in the database that get written to. Everytime a new report is completed the "Rep ID" field gets incremented as it is the primary key.

2. I now need to add the ability to update thes "Reports" once they are already submitted. I have created an "Updates" table in the SQL database
- This table also has a "Rep ID" field that references the main table, in the relationship I removed the check box for "Enforce relationship fir replication". I had to do this before I was able to add data to the table from submit in Infopath.

3. I have added the ability to choose a completed report from a drop down list box that points to the database via a secondary datasource. This list box is bound to the Main Datasource "Rep ID"
- When choosing a report number from this drop down list box it populates all the already completed infomation (all tables except the new update table I created)

4. Once a report is selected to be updated here I have a command button that switches to a view that only has the Entries for the new Update Table (The "Rep ID" selected from the dropdown is passed on submit to the "Rep ID" for the Updates Table.
- Once the info is entered in the form a Submit button will (should) add these valuse to the Updates table on the database - but it doesn't.

I get the following error: Infopath cannot submit the form. An error occurred while the form was being submitted. The value of an ID key or timestamp has been changed. Id keys and timestamp columns are automatically generated by the data source and cannot be changed.

Problem:

I'm pretty sure my problem is that I need to use the main data connection to populate all of the already completed fields (Each field needs to be bound to the main datasource as it is "repeating", because of this the "Rep ID" with is the Primary Key on the main table will not allow it to be updated (I dont want it to update anything but the new "Updates Table")

Question:

Is there a way to populate these fields with a secondary data source?
- when I try to change binding I get "You have chosen to bind a non-repeating control to a repeating field or group. This control will be bound to the first item in the repeating field or group."

If I cant do above is there a way to tell INFOPATH on Submit to only submit to the one "Updates" Table, even though the main data source has all tables selected?

Also I am not a programmer in any way and have therefore not done any coding on the submit button.

Thank you to anyone who can help with this, I joined up just for this.
- Mark
0
mchestnut26
Asked:
mchestnut26
  • 4
1 Solution
 
MelMcCommented:
From my experience when you have multiple tables in your Main Connection you have no choice but to submit to all or none. I am sure that this could be done in code, but I try to avoid that. Could you possibly create another form that has a query so that the user can look for the proper item in the Update table and then submit changes/new items?
0
 
mchestnut26Author Commented:
Thanks MelMc,

Ok,
I did try this solution by creating a new form from the database connection, I only selected the update table. This way I was able to add records to the update tables via the form.
The problem however still remains that I am unable to auto fill the form so the user can view the actual data for the "Rep ID" number they chose from the drop down.

 Is there a way in Infopath other than using Repeating controls (which I can only get the main data source to work with) to display the current data for the chosen "Rep ID" from the drop down list box?
I only want to submit to the "Update" table (Primary Data Source)  but view record in the other table Secondary Data Source.

If setting up a query will work, how do I go about doing that?

Thanks Again
0
 
mchestnut26Author Commented:
Hi Again,
Upon further research I don't think binding a secondary data source to a field in this way is possible.
Can any of the below processes possibly work for me? If so could someone give me a nudge in the right direction?

1. Setup custom code on a command button to populate fields in the SQL Database.
   - I only have 7 fields in this table.
   - with this method (if possible) I can have the form load with values from the main data source but
      have the submit button only pass these 7 fields that the user will fill in to the database.
   - with this I should no longer get the "...ID key or timestamp has been changed..." error upon
      submission.

2. Setup a command button in Infopath to open an entirely different form based on the value the
     user selects from the drop down list box.
   - The initial form can still have the field bound to the main data source as it will never submit from
      this form
   - The user then clicks the new command button that opens a different Infopath form and passes
     the value selected in the dropdown to the "Rep ID" field on the new form
   - The new forms main data source will only be the "Update" table so submitting will not be a
     problem
   - Also since the "Rep ID" is taken from the Initial form and passed in it will not allow user
     modification which will insure it matches the "Rep ID" of the other tables for reporting later on

Thank you for any help you can provide,
Mark
0
 
mchestnut26Author Commented:
Also for Clarification, the user needs to be able to see what is already in the database so they can be sure they are updating the correct "Rep ID"
- Mark
0
 
mchestnut26Author Commented:
I have resorted to creating a new form just for updates. I had this form auto fill a few fields from the main report so the user when know they are updating the correct one.

It seems to me that this is the only way to add records to only one Table in a database (Without using Sharepoint).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now