Results 1 to 5 of 5
  1. #1
    sjhime is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    9

    Many Contacts with Many companies and need to add new contact in form

    My apologies if this has been posted already. I searched the forum but couldn't find anything similar. I have a database where a contact could be an employee of many companies and where company has many employees. I've dealt with this by having an intermediary table with Contact_ID and Company_ID. I have a query to show the data and the query works great. However, I need to have a form where others can enter data. I can enter data into a contact table, a company table, and other related tables, but the issue comes in when I have to tie the contact to the company in the tbl_Contact_Company table.

    I've attached an image of the relationships. Right now, I'm thinking of having a form with the company information and a sub-form with the contact information. But, I'm still left with how to add a new contact. I've worked on this for a few months and am left scratching my head. Looking for suggestions! Thank you!



    Click image for larger version. 

Name:	tbl_Contact_Company Relationship.PNG 
Views:	34 
Size:	37.2 KB 
ID:	37308

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Use the wizard to create a form for tbl3_contact_new.
    Use the wizard to create a form for tbl_Contact_company.
    Open the first form in design view.
    From the navigation pane, drag the second form onto the first to create the subform. Show the subform in datasheet view.
    Last edited by davegri; 02-06-2019 at 11:41 AM. Reason: added subform detail

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would have the subform bound to the junction table (tbl_Contact_Company), not the contact table. Master/child links will keep the subform in sync with the main form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    In case a contact person can be employed by several companies, and you need to specify the company too (and you have such information), you need a table where for every contact person are listed his/her employers
    tblCompanies: CompanyId, CompanyName, ...
    tblContacts: ContactID, Forename, LastName, Phone, email, ...
    tblContactCompanies: CC_ID, ContactID, CompanyID

    For your contact data, you save CC_ID as foreign key in your tables. The best way is to select the contact from combo box, with row source like
    Code:
    SELECT cc.CC_ID, cont.Forename & " " cont.LastName & " (" & comp.CompanyName & ")" AS Contact FROM (tblContactCompanies cc INNER JOIN tblContacts cont ON cont.ContactID = cc.ContactID) INNER JOIN tblCompanies comp ON comp.CompanyID = cc.CompanyID
    In this way you avoid using cascaded combos when registering a contact.

    The same setup works also, when you have a list of companies you work with - simply add according WHERE clause for row source query of combo.

  5. #5
    sjhime is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    9
    Thank you everybody for the great advice! I'm still a bit confused. I have 2 forms now, one form for the company and 1 form for the contact. The Contact form is set as a subform in the Company form. I have an unbound combo box that serves as a record selector with this code:

    Code:
    SELECT tbl_Company.Company_ID, tbl_Contact_Company.ID, tbl3_Contact_NEW.Firstname & " " & tbl3_Contact_NEW.LastName & " (" & tbl_Company.Company & ")" AS Contact
    FROM (tbl_Contact_Company INNER JOIN tbl3_Contact_NEW ON tbl_Contact_Company.Contact_ID = tbl3_Contact_NEW.Contact_ID) INNER JOIN tbl_Company ON tbl_Contact_Company.Company_ID = tbl_Company.Company_ID;
    The selector box works great. I can select a Company and see each contact in the subform. I'm still having some issues trying to add a new contact. I know that I can write some code to add the contact to the tbl_Contact_Company table but I was hoping not to write a ton of code to just add a contact. I would appreciate any other suggestions people have. Thank you again!!

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

Similar Threads

  1. Replies: 3
    Last Post: 08-18-2017, 07:26 AM
  2. Replies: 1
    Last Post: 07-15-2017, 11:23 AM
  3. Replies: 4
    Last Post: 12-16-2016, 01:24 PM
  4. Replies: 4
    Last Post: 04-29-2014, 10:35 AM
  5. Replies: 5
    Last Post: 04-20-2014, 10:51 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