Customizing Submit Functionality

[Mullie21]

I have an form linked to a MS Access DB and I'm having some trouble
submitting the form. Here is the scenario: I have one table for financial
data. This financial data has two identifiers, date and project. Data for
the date and project are stored in two separate tables joined in Access with
keys.

What I'm trying to accomplish is allow the user to input the project and
date once, and then input multiple records into the financial data table
utilizing the project and date. So, to do this at the top of my screen I
have two cascading lists, one for project and one for date that query the
information from the database. Then I have a repeating section which pulls
the date and project from the header and allows the user to enter the
detailed financial data.

Everything is working perfectly in the form. I am having issues when I
submit the form. From the best that I can tell, the form is not only
submitting the data in the repeating table to the database, but it is trying
to create new records in the project and date tables as well. The result is
an error when I submit that says submission of the data will create duplicate
records in the database (records with the same primary key).

Is there any way that I can prevent the project and task data from being
submitted to the database and only submit the data in the repeating section?

Any other advice is appreciated as well.

Thanks,

Chris

 

[Mullie21]

Greg,

Thanks for your quick response. I'm not sure what you mean exactly. Are
you saying that I need a Web Service to sit between InfoPath and MS Access?
This is showing my ignorance, but what is a Web Service and what does it do?

Also, would upgrading to SQL Server allow me to fix this problem?

Thanks,

Chris

 

[Mullie21]

Greg,

Thanks for your advice. If I remove the other tables from the main data
source, how will I be able to do the cascading lists? When I set them up,
the filter relies on the stored data. Is there a way around this?


Anyone else have any advice on this??????

Thanks,

Chris

It might be possible to get away without one. I have not done a lot with submitting to Access/SQL. It could be that there is just something different you can do to prevent the error you are receiving. One possibility is to make your main data source to be just the repeating table of data that you are having users update, and use secondary data sources to get all the other non-changing information.

If, however, none of this works, another option is to use a Web service. This will allow you to control exactly what data is submitted and in what way.

As for how to possibly fix the issue you are having, I'll have to defer that to anyone else reading this thread that has experience with that.

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



Greg,

Thanks for your quick response. I'm not sure what you mean exactly. Are
you saying that I need a Web Service to sit between InfoPath and MS Access?
This is showing my ignorance, but what is a Web Service and what does it do?

Also, would upgrading to SQL Server allow me to fix this problem?

Thanks,

Chris

The create a custom submit with a subset of data you will be best served with a Web Service.

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



I have an form linked to a MS Access DB and I'm having some trouble
submitting the form. Here is the scenario: I have one table for financial
data. This financial data has two identifiers, date and project. Data for
the date and project are stored in two separate tables joined in Access with
keys.

What I'm trying to accomplish is allow the user to input the project and
date once, and then input multiple records into the financial data table
utilizing the project and date. So, to do this at the top of my screen I
have two cascading lists, one for project and one for date that query the
information from the database. Then I have a repeating section which pulls
the date and project from the header and allows the user to enter the
detailed financial data.

Everything is working perfectly in the form. I am having issues when I
submit the form. From the best that I can tell, the form is not only
submitting the data in the repeating table to the database, but it is trying
to create new records in the project and date tables as well. The result is
an error when I submit that says submission of the data will create duplicate
records in the database (records with the same primary key).

Is there any way that I can prevent the project and task data from being
submitted to the database and only submit the data in the repeating section?

Any other advice is appreciated as well.

Thanks,

Chris