Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: ACCESS 2007 Autocreate child table entries on parent insert

    I am trying to create a database which will have multiple tables child tables of one primary table. each child table is tied to the parent table using the parent table's primary key as thier own primary key (each relationship is a 1-1 with the same key value being used as the primary key value for both tables). for example, a parent table of student (containing the student's name, grade) would have a child table for the student's mother, another child table for the student's father, and another table for the student's address- see below (I understand that givin this example, this would not be the best table setup, but it is only a simple example of what I am running into).

    Table 1. Student (Parent table)

    * ID (Autonumbered PK)
    * First Name
    * Last Name
    * Grade

    Table 2. Mother (Child Table)

    * ID (PK, not autonumbered, tied to Student PK)
    * First Name
    * Last Name

    Table 3. Fother (Child Table)

    * ID (PK, not autonumbered, tied to Student PK)
    * First Name
    * Last Name

    Table 4. Address (Child Table)

    * ID (PK, not autonumbered, tied to Student PK)
    * Street Address
    * City
    * State
    * Zip
    I am looking for a way to setup a macro to run on creation of a new student that will create a corresponding blank entry in each of the child tables containing only the primary key value to tie them together. To make matters more complicated (at least in my own mind) the primary key for the parent table is an autonumber field, so I need to somehow capture that, and then create the additional entries using that number.

    Does anyone have any suggestions (BTW- if you haven't already figured it out, I am really new to Access programming, so if you need more info, please let me know)?

  2. #2
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    I would probably use an SQL INSERT INTO query to achieve the automatic one-to-one table relationship. As for your autonumbered parent table, you could add a field to it that references the Child's ID.

    Code:
    INSERT INTO Mother ( ID )
    VALUES ( <Child's ID> )
    You may be able to tie this into the On Insert property of your Child table.

Posting Permissions

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