Data Entry Form - Multiple Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kim - 31 Aug 2005 21:04 GMT

I need to set up a data entry form (using SQL). I have six relational tables
and read that you can only have 1 child table. If you have more then the form
is read-only and you cannot submit data. Is this true?

Reply to this Message

Scott L. Heim [MSFT] - 01 Sep 2005 20:07 GMT

Hi Kim,

Are you talking about 1 "parent" table that has 5 "child" tables or are you
talking about something like this:

- Customers (parent)
---> Orders (child of Customers but parent of Order Details)
------> Order Details (child of Orders)

Thanks,

Scott L. Heim
Microsoft Developer Support

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

Reply to this Message

Kim - 01 Sep 2005 20:34 GMT

This is the situation (I hope it makes sense)

-Customers
--Attributes

-Customers
--Category
---Category Master

-Customers
--Multiple Attribute
--Attribute Master

All the tables are linked that way in the database.

> Hi Kim,
>
[quoted text clipped - 11 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights

Reply to this Message

Scott L. Heim [MSFT] - 01 Sep 2005 21:25 GMT

Hi Kim,

I just completed a test with a total of 3 tables and it works without
issue. Here are my tables and fields - if you could test this in a new
Access database that would be great!

- Create the following tables and fields:

    - tblCustomers
        CustomerID, AutoNumber, Primary Key
        CustName, Text

    - tblAddresses
        AddressID, AutoNumber, Primary Key
        CustomerID, Number
        Address, Text

    - tblPhones
        PhoneID, AutoNumber, Primary Key
        CustomerID, Number
        PhoneType, Text
        PhoneNum, Text

- Create relationships (in Access) between the tables as follows:

- tblCustomers -> tblAddresses on CustomerID
- tblCustomers -> tblPhones on CustomerID

** NOTE: I did choose the option: Enforce Referential Integrity

- Close Access
- Create a new (from Data Connection) InfoPath solution pointing to the
above database
- When you begin to add tables:

    - Add tblCustomers
    - Add tblAddresses and this should automatically create a relationship on
CustomerID
    - Highlight tblCustomers before adding tblPhones

- Once complete, from the Data Source Task Pane, drag "d:Customers" to your
form and choose the option: Repeating Section with Controls -this should
add tblAddresses and tblPhones as Repeating Tables
- Preview the form
- Add your name to the CustName field, some dummy data to the Address,
PhoneType and PhoneNum fields
- Click the Submit button - this should successfully submit back to your
Access database

Let me know if this works for you!

Scott L. Heim
Microsoft Developer Support

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

Reply to this Message