Results 1 to 7 of 7
  1. #1
    kevrizz
    Guest

    Adding related records (2000)

    I have 4 tables, 3 of which are linked via a one-to-many relationship to the main table. The link is a field caled Company name. I am trying to work out how I can add a new company to the main table and have that company automatically added to the other tables. I am currently adding the new company via a form but cannot get the other fields to update.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding related records (2000)

    One solution is to use a query to join the tables together, but with 3 linked tables I suspect your query may not be updateable. It's worth a try though, as the other alternative involves writing a bunch of code to do appends to each of the tables after the main table is populated. If you try the join approach, make sure all the joins are equi-joins.

    You may want to look at your table design too. If all tables require data all the time, would it make sense to just have one table and eliminate the three linking fields? Normalization can be a tricky issue. Hope this helps.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Adding related records (2000)

    >>I have 4 tables, 3 of which are linked via a one-to-many relationship to the main table. The link is a field caled Company name. I am trying to work out how I can add a new company to the main table and have that company automatically added to the other tables. I am currently adding the new company via a form but cannot get the other fields to update. <<

    I'm having some problem understanding what you are trying to do, maybe it is your terminology. Something doesn't sound right from what you are describing. What do you mean by " have that company automatically added to the other tables" and "cannot get the other fields to update"?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    kevrizz
    Guest

    Re: Adding related records (2000)

    Mark
    One table is Company Info with Company Name as Primary Key. This is the foreign key in 3 other tables for Training History, Contact Details and Business Units. Links are one-to-many.
    I am trying to set up a form so that users can add a new company to Company Information. At the same time I'd like that company to appear in the other tables. This is so that the details from all tables can be seen in a form with subforms for the other tables.
    At present if I add a new company the subforms for Training History, Contacts and Business Units are blank i.e. no fields at all, not no data.
    Hope this explains my problem.

    Kevin

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Adding related records (2000)

    You will have to manually insert a record into each of the child tables, if you want an essentially "blank" record to appear for in each of them for the new company. You could use append queries, firing them during your form's AfterInsert event. However, if any of the fields are required and don't have default values, the append queries will fail.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    kevrizz
    Guest

    Re: Adding related records (2000)

    Thanks Mark
    It's amazing how clear things become after discussing it with others. My answer is to do exactly as you said, to manually enter the values via some code. The form prompts the user for a new company name and upon entering it it is added to the other tables. Thanks again.

  7. #7
    kevrizz
    Guest

    Re: Adding related records (2000)

    Wendell, I've adopted your "bunch of code" suggestion. I use a form to collect the new name and then have it added to the other tables. Thanks.

Posting Permissions

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