Results 1 to 4 of 4
  1. #1
    ferrets is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7

    Question How to update records in a sub-form for a many-to-many relationship

    Hello,

    Apologies is this is fairly basic or if the answer has already been posted elsewhere. I'm new to this!



    I have a database with two tables: Organisations and People. An organisation can comprise of many people and the people can also be members of more than one organisation. To show these links I created a third table which contains the OrgID and PeopleID. I then defined the relationships between the three tables to create a many to many link between Orgs and People.

    After that I created a main Organisation form. I then created a query which included the IDs from the Link table and the fields from the People Table. I created a subform from the query and insert it into the main organisations form. I could then link the organisations to people by changing the person name into a combo field, row source to the person ID etc.

    This all worked fine if I had people that were already created and I wanted to link them to the organisation.

    However, I would like to be able to create new people records from the organisation subform, thereby directly linking the two. I just can't figure out how to do this last bit.

    Any help would be appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, creating lookup records 'on the fly' is a very common question.

    Here is one tutorial for one method http://www.fontstuff.com/access/acctut20.htm
    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.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    ferrets is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7
    Brill. Many thanks!

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

Similar Threads

  1. Replies: 3
    Last Post: 08-10-2017, 09:07 PM
  2. Replies: 3
    Last Post: 08-14-2016, 02:18 PM
  3. Replies: 2
    Last Post: 07-07-2016, 07:01 PM
  4. Replies: 3
    Last Post: 08-10-2015, 04:44 AM
  5. Replies: 2
    Last Post: 01-09-2012, 02:40 PM

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
  •  
Other Forums: Microsoft Office Forums