Results 1 to 8 of 8
  1. #1
    laurenG is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    2

    How to add a new record from a form to all parent and child tables?

    Hello, and thanks in advance for anyone offering help on what is probably (hopefully) a very easy problem to solve...

    I have one parent table with 6 child tables (all child tables are linked to the parent table in the relationship through the BoardID autonumber field, and they are all one-to-one relationships). All of the tables feed into one form. I have added the "add new record" button to the form, but when I click on it, it only adds a record to the parent table. The only way I have found to solve this is to go into each individual table and add a new record. Is it possible to have a new record added to every table when I click the "add new record" button on the form?



    Thanks.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are telling us how you have done something and plan to continue. It would be helpful to me if you could tell us what the subject matter is? What are you trying to do in plain English -no Access jargon?

    You may want to work through this tutorial, then address your own issue/opportunity with what you have learned.
    Good luck.

  3. #3
    laurenG is offline Novice
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    2
    Thanks.
    I have a database of potential board candidates. My parent table contains contact info, personal details, ect for each candidate (unique ID is BoardID, an autonumber field). Each child table contains other information about them (tblEducation, tblProfession, tblGovernanceService, etc. all of these tables have a unique ID of BoardID, an autonumber field). We have a lot of data for each candidate which is why I have separated it into all of these tables.

    I have created a form (FrmCandidates), and all of these tables feed into this one form. This is a database which needs to grow, as we are adding new candidates all of the time. I need to be able to add new records, so I have placed an "add new record" button on the form. However, when I try to add a new record by clicking on this button, the only thing it does is add a new record to my parent table. None of the child tables get a new record. How do I fix this?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Normally a form can do data entry/edit for only one table.

    Build form/subform(s) arrangement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    If you have an unbound form you can use coding to place data in different tables. Depending on size of db could be a LOT of coding and regardless is a large amount of work and continual tweaking to get it right.

    You could have links to other forms to, let's say, quickly add a profession to tblProfession so you can then select that for your current entry.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a database of potential board candidates.
    My parent table contains contact info, personal details, ect for each candidate (unique ID is BoardID, an autonumber field).
    Each child table
    contains other information about them (tblEducation, tblProfession, tblGovernanceService, etc. all of these tables have a unique ID of BoardID, an autonumber field).
    You are going to have troubles.

    If you have 7 tables that have a PK field named "BoardID" and they are all AUTONUMBER, you probably will never get them related. And it is confusing to have all tables with the same PK field name.
    You cannot force an autonumber field in one table to equal (be the same as) the autonumber field in another table. It is very rare to have a true one-to-one relationship.

    Having 7 tables related in a one-to-one relationship is, IMO, the wrong way to go.
    I would have the parent table (master) with Autonumber PK field and the 6 child tables a FK (Long).
    the child tables still have autonumber field as PK field. No problems with 7 autonumber PK fields.

    Now there is no problem creating a new parent table (master) record.



    My $0.02..................

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Steve has a good point (I did not notice OP statement that all tables have autonumber BoardID field).

    Tables CANNOT be linked on autonumber fields. Parent table can have autonumber PK field and related child tables must then have a number (long integer) field for FK field. PK of parent table is saved as FK in child tables. This is what form/subform arrangement automates - synchronization of related records.

    Child tables can also have an autonumber field to be a PK but really not necessary unless they also have dependent tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    One fix is to duplicate Parent PK in each child table under data type "number" then redefine your Parent/child relationships based on one-many. Afterwards you should be able to add new records. good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  2. No (+) for parent-child tables in linked database
    By Seamus59 in forum Import/Export Data
    Replies: 3
    Last Post: 11-18-2013, 11:39 AM
  3. Replies: 4
    Last Post: 09-13-2013, 05:26 PM
  4. Replies: 3
    Last Post: 07-03-2013, 01:20 PM
  5. Delete child records with parent record
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 09-07-2010, 08:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums