Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    update records (keys) on related sister tables

    In an Access database, I have a Volunteer file, and associated with it and with the same primary key value as the Volunteer table, are AdditionalInterests, Education, and other files. When I write out a record to the Volunteers file, I want to write records with the same primary key value out to the sister files, so that when the user has time to enter the extra information, the placeholder (primary key) is already there and the correct record is updated, and enforces the relational nature of the system.

    what's the best way to write to the other few files when I write out a volunteers record?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    Sounds like a classic case for using subforms, providing I understand your question.

    In the related tables (the "child records"), they would each have their own primary key, and they would each have a "foreign key" (which is the primary key from the parent table). I am assuming this is what you want, so that multiple Interests can be added relating to a single volunteer, for example.

    For example:
    Code:
    VolKey   Name
    1        Tom
    2        Dick
    3        Harry
    
    
    IntKey   VolKey   Interest
    1        1        Programming
    2        1        Bowling
    3        2        Programming
    4        3        Cooking
    4        3        Soccer
    have fun,
    tc

    Small, custom, unique programs
    email
    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Sep 2012
    Posts
    5

    yes, but...

    if they don't have any or all the information for the other tabs, those files don't get created, and there's no "placeholder" for them when they go back in to enter that tab's data later on.

    can i not just write a primary key on a file on the AfterUpdate hook on the form properties?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,001
    sure, but why? in the parent child relationship model, it's ok if you don't have any children at first (or ever) for some of the records. Based on what I know of your question at the moment, it doesn't make sense to create essentially empty records to "hold a place" for potential future child records. The sub table containing the child records will have it's own unique primary key, and will happily accept the foreign key (the parent) at any time.

    When you go back to add child records later, you open the form back up, select the volunteer and then start adding interests, etc.

    In fact, if your child table is one-to-many (multiple interests per volunteer, for example), the primary key of the child has to be unique (and independent of the parent key) anyway.

    Am I missing something in your question?

Tags for this Thread

Posting Permissions

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