On Infopath, do all the Access tables need to be directly related?

[Hector]

Hi. I'm Hector.
I have an Access database with four tables, and I created an Infopath form
using the data connection wizard, etc. The problem is that once I input all
the data, the form can't be submitted to my database saying that there may be
a many-to-One relationship.

My tables are like this:
tblCustomer --> One-to-Many --> tblAccount (Customer can have many accounts).
tblCustomer --> One-to-Many --> tblCall (Customer can make many calls
in a week or even in a day).
tblPhoneAgent --> One-to-Many --> tblCall (PhoneAgent attends many
customer calls in a week or even in a day).

On infopath I used tblCustomer as my primary table. And I really need all
the fields (except, of course, the keys) from all the four tables on the
infopath form.

Am I doing something wrong on my database? Or what should I do to make the
infopath form work?

Thanks in advance.

 

[Clay Fox]

Hi Hector.

You have run into one of the limitations of the default database form. You
can only have one primary table and its related children that can submit.
Phone Agent, since it is not related to customer directly, is what is getting
you.

A couple options.

Update Phone Agents in another form and just have a secondary recieve
connection in your main form to select a phoneagent to link to the calls.

Look at using WebServices such as the Database Accelerator from
http://www.qdabra.com. Then you can identify your from users via active
directory and have multiple queries and submits in one form.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Hector]

Okay, let me see if I understood you. Create a separate form using only the
phone agent table? Then, on the main form, add a secondary receive conncetion
of the phone agent table.?

I tried doing the secondary connection before, but it doesn't let me do
anything with it. I just don't understand how the secondary connection works.

Thanks.

 

[Clay Fox]

By default a secondary connection will pull in all of the data in the
specific table. Then you can use a filter to get what you want.

If you use Web Services, you can have dynamic queries of tables.

I am assuming that the Phone Agent is the form user. So identify the form
user name when the forms opens, then use this to lookup the phone agent's ID
in the table and then use that when adding call records.

If you need to edit or add Phone Agent records in your table have a seperate
form which does this.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Hector]

Yes, you are correct, the Phone Agents are going to be the form users.
Can you tell me how to do that? I'm a newbie at Infopath....

Thanks.