Can I Submit to a third data source

[mdsmitty]

I have a form created from an SQL database and have added 3 tables from that
database as my datasoucres. What I am trying to do is submit data to the 3rd
table. The workflow of my form is as follows.

1. An account number is entered into the form.
2. This account number is contained in all 3 tables.
3. Table 2 contains the demographics of the account and populates
demographic fields within the form.
4. Additional fields within the form will be filled out by the user and
will need to be submitted to Table 3.

Is this possible in InfoPath and if so, what is the best way to accoplish
this?

Thanks in advance.

 

[Scott L. Heim [MSFT]]

Hi,

Are all the tables part of your "main" data source? If so, then when you
submit back to the database they should be updated. However, if any of the
tables are "secondary" data sources and these will need to be updated as
well then you will need to override the built-in submit functionality and
use custom code (look at the Submit method) to submit to any and all data
connections you have.

For instance, this is the same basic process you would use if you wanted to
submit to SQL and send an e-mail at the same time.

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[mdsmitty]

All 3 tables are part of my main datasource...I want to use Tables 1 and 2 to
populate the form and submit data to Table 3. The fields that are being
populated from tables 1 and 2 will be "read only". The issue seems to be
that I can't use more than 2 tables in my Main Datasource. The Submit
function from Tools->Submitting Forms does not give me a Database selection
from the drop-down in the Dialog Box when I have more than 2 tables. I now
assume that I will need to write VBScript in order to accomplish this.
Unfortunately I am very new to writing code and don't know how to put all
this together.

I have been going to the sites that have been suggested by yourself and Greg
to get sample code but at this point I can't find the sample code or I am not
modifying the code correctly when applying it to my form.

Any further help will be appreciated, thanks again for you help.

 

[mdsmitty]

I got it to work....the key in Table 3 was not set.

 

[mdsmitty]

Next problem...I want to be able to add new records to Table 3 within my data
source. Here's how I am attempting to do this (without success at the
moment).

I have in Table 1 an Account Number (known to the user) and a Patient Number
(unknown to the user). Table 2 also contains the Patient Number which gives
me the connection between the two tables. Table 3 is blank and will contain
the Patient Number as well as information input into the form by the user. I
want to be able to populate Table 3 "on the fly". All 3 tables are part of
the "Main" datasource.

The issue seems to be that InfoPath will now allow me to select “Databaseâ€
from the Submit Dialog box (it is not available on the drop-down list). I
have tried to submit the information to the database but I don’t have the
correct code. The following error occurred:

InfoPath cannot submit the form.
Invalid context for OM call.
XDocument.Submit cannot be called from the XDocument::OnSubmitRequest handler.

File:script.vbs
Line:38

Can I do this with one form or will I need to create multiple forms/views?
Any thoughts?