Trouble with one to many connection with an Access2007database

[Ryan Cheng]

I need some help for a project I am doing.

I am using infopath2007 with an Access2007 database.

I have 3 tables
1)tblInformation (Name, ID, startdate...) //non-repeating
2)tblFunding (ID, Participants, Amount...) //repeating
3)tblApproval (ID, Name, Position, Status...) //repeating

I want all these related by ID in a One to Many relationship.
(Unfortunately, Infopath wont let me submit data if fund and approve dont have primary keys [interpreting a M:1 relationship] so I made a unique index# for each table and related those aswell.)
tblInformation -1:M- tblFunding
tblInformation -1:M- tblApproval
(aka a single form ID can have multiple instances of funding and approval)

I have 1 nonrepeating section for Info and 2 repeating sections for funding and approval.

Problem: When I try to submit with a section repeated more than once i get a 'Empty row cannot be inserted. Row must have at least one column value set.'

I assume this is happening
tblInfo | tblFunding | tblApproval
stuff | stuff | stuff
null(error) | cant submit more stuff
^because i only enter tblInfo once.

Im not sure how to get around this issue because Infopath and Access have so many restrictions. Any help would be greatly appreciated.




EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[K.Ramana Reddy(GGK Tech)]

Hi,

Please provide schema of your infopath, Based on that we can design database
schema,

If you have SQL installed in your system, better way to to download the
database
accelerator.

Check out this link for more information.
http://www.qdabra.com/proddetail.asp?prod=QDBXL1

I hope this will help for you.

 

[chenhuyr]

Hi,

Thanks for replying. I am still pretty new to infopath. Would you like me to
copy and paste the schema.xsd file? There are is schema, schema1 and schema2
so it might be quite long.

side note: I do not have VS or VSTO at the moment. Am I able to create this
form without it?(can be answered later)

Hi,

Please provide schema of your infopath, Based on that we can design database
schema,

If you have SQL installed in your system, better way to to download the
database
accelerator.

Check out this link for more information.
http://www.qdabra.com/proddetail.asp?prod=QDBXL1

I hope this will help for you.

--
k.Ramana Reddy
http://www.GGKtech.com

I need some help for a project I am doing.

I am using infopath2007 with an Access2007 database.

I have 3 tables
1)tblInformation (Name, ID, startdate...) //non-repeating
2)tblFunding (ID, Participants, Amount...) //repeating
3)tblApproval (ID, Name, Position, Status...) //repeating

I want all these related by ID in a One to Many relationship.
(Unfortunately, Infopath wont let me submit data if fund and approve dont have primary keys [interpreting a M:1 relationship] so I made a unique index# for each table and related those aswell.)
tblInformation -1:M- tblFunding
tblInformation -1:M- tblApproval
(aka a single form ID can have multiple instances of funding and approval)

I have 1 nonrepeating section for Info and 2 repeating sections for funding and approval.

Problem: When I try to submit with a section repeated more than once i get a 'Empty row cannot be inserted. Row must have at least one column value set.'

I assume this is happening
tblInfo | tblFunding | tblApproval
stuff | stuff | stuff
null(error) | cant submit more stuff
^because i only enter tblInfo once.

Im not sure how to get around this issue because Infopath and Access have so many restrictions. Any help would be greatly appreciated.




EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[chenhuyr]

Here is my schema. If you need anymore just let me know.
Thanks for the help.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema
targetNamespace="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
elementFormDefault="qualified" attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
xmlns:q="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields">
<xsd:import
namespace="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
schemaLocation="schema1.xsd"/>
<xsd:import
namespace="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields"
schemaLocation="schema2.xsd"/>
<xsd:element name="myFields">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="queryFields">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="q:tblAFEIdentity" minOccurs="0" maxOccurs="1"/>
<xsd:element ref="q:tblAFEFinance" minOccurs="0" maxOccurs="1"/>
<xsd:element ref="q:tblAFEApprovals" minOccurs="0" maxOccurs="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="dataFields">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="d:tblAFEIdentity" minOccurs="0"
maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:any namespace="##other" processContents="lax" minOccurs="0"
maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:anyAttribute namespace="##other" processContents="lax"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

I need some help for a project I am doing.

I am using infopath2007 with an Access2007 database.

I have 3 tables
1)tblInformation (Name, ID, startdate...) //non-repeating
2)tblFunding (ID, Participants, Amount...) //repeating
3)tblApproval (ID, Name, Position, Status...) //repeating

I want all these related by ID in a One to Many relationship.
(Unfortunately, Infopath wont let me submit data if fund and approve dont have primary keys [interpreting a M:1 relationship] so I made a unique index# for each table and related those aswell.)
tblInformation -1:M- tblFunding
tblInformation -1:M- tblApproval
(aka a single form ID can have multiple instances of funding and approval)

I have 1 nonrepeating section for Info and 2 repeating sections for funding and approval.

Problem: When I try to submit with a section repeated more than once i get a 'Empty row cannot be inserted. Row must have at least one column value set.'

I assume this is happening
tblInfo | tblFunding | tblApproval
stuff | stuff | stuff
null(error) | cant submit more stuff
^because i only enter tblInfo once.

Im not sure how to get around this issue because Infopath and Access have so many restrictions. Any help would be greatly appreciated.




EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com