Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    UK
    Posts
    12

    Unanswered: Adding records to multiple tables via a single form

    Hi,

    Hope you can help...

    I have a number of related tables in a database and would like to use a single form to add/edit records in these tables.

    Not all fields of each table need to contain data.

    I will describe the table structure and relationships to try and clarify:

    Table1:
    Table1ID - PK
    Table2ID - FK
    Field1...n


    Table2:
    Table2ID - PK
    Field1...n


    Table3:
    Table3ID - PK
    Table2ID - FK
    Field1...n


    Table4:
    Table4ID - PK
    Table2ID - FK
    Field1...n


    Table5:
    Table5ID - PK
    Table2ID - FK
    Field1...n

    OK.
    Table1 contains data on entities and these entities can be subclassified.
    Tables3-5 contain subclassification data specific to the initial entries in Table1.
    Table2 is somewhat unusual in that can contain multiple records for each unique entity in Table1, reflecting the ability of an entity to be transformed from one to another subclassification!

    Table1 will ALWAYS contain a record.
    Table2 will ALWAYS contain a record.
    Table3 will sometimes contain a record.
    Table4 will SOMETIMES contain a record.
    Table5 will SOMETIMES contain a record.

    If Table 4 contains a record then Table5 will NOT contain a record.
    Table3 will only contain a record if there is a record in either Table4 or Table5.

    Right, if you've managed to wade through that, here is what I would like to be able to do;

    I would like users to be able to add and edit data held in these tables from a single form. The order in which data will be entered is Table1, Table3/4(and 5), then Table2!!!! The data should be added when the user clicks on an "OK" button.

    As the Tables3-5 require a Table2ID FK I can't figure out how this would be best achieved....
    The table structure/design can NOT be modified for a number of reasons.

    Any help you could offer would be greatly appreciated!
    I can attach a copy of a portion of the database if that helps...


    Despairingly,

    John
    Last edited by genisys; 12-27-03 at 15:56.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well looking at your table layout I'd say that table 2 records have to added 1st especially if you have relationships. The only way to do otherwise is add the records in the other tables and then to table 2 and THEN GO BACK and update the previously added records to hook to table 2 ... Something to think about: tables 3 thru 5 MAY have a record ... So the only tables REQUIRING records are 1 & 2 and since 1 has a dependency on 2 well you do the math ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •