Solved Enter data into two tables

[sctb0825]

I am using Access 2021. I have two tables Customers and Customer Contacts. One contact may represent multiple Customers or one Customer may have multiple Contacts.
I want to be able to enter the contacts code or ID into both tables no matter which form I am entering data on, the only data that is common is the Contact ID. on my Customer form when I double click the Contact ID it brings up the Contact form to enter the contact data but I want the Contact ID to populate both tables.

Is this possible? and how do I do it?

 

[GPGeorge]

You have a many-to-many relationship which requires THREE tables, not two.

Customers
Contacts
CustomerContacts

The CustomerContacts table has two Foreign Key fields, one from the Customers table and one from the Contacts table. That allows a contact to represent many customers, and it allows a Customer to employ many contacts.

The way to add the records to the CustomerContacts table is through a Main Form/Sub Form design.

The main form is bound to the Customer table. The sub form is bound to the CustomerContact table. The Master/Child Linking property of the subform control manages the insertion of the appropriate Customer ID into the CustomerID foreign field in the CustomerContact table. You put a combo box, bound to the ContactID field in the CustomerContact table, in the subform. The row source for that combo box is the Contact table.

You'll find a demo database here.

 

[jdraw]

Can you show us your tables and relationships? (expanded so we can see all fields)

 

[sctb0825]

You have a many-to-many relationship which requires THREE tables, not two.

Customers
Contacts
CustomerContacts

The CustomerContacts table has two Foreign Key fields, one from the Customers table and one from the Contacts table. That allows a contact to represent many customers, and it allows a Customer to employ many contacts.

The way to add the records to the CustomerContacts table is through a Main Form/Sub Form design.

The main form is bound to the Customer table. The sub form is bound to the CustomerContact table. The Master/Child Linking property of the subform control manages the insertion of the appropriate Customer ID into the CustomerID foreign field in the CustomerContact table. You put a combo box, bound to the ContactID field in the CustomerContact table, in the subform. The row source for that combo box is the Contact table.

You'll find a demo database here.

The link did not work, Thanks for the explanation can you fix the link

 

[GPGeorge]

The link did not work, Thanks for the explanation can you fix the link

What DID happen? "The link did not work" could mean multiple things.

 

[Gasman]

What DID happen? "The link did not work" could mean multiple things.

Does not work for me either, until I copy link address. I just stay on this page.
My issue probably due to Malwarebytes Browser Guard, as it asked me if I wanted to go to the site, when I pasted the link url.

 

[sctb0825]

The link did not work, Thanks for the explanation can you fix the link

So since the only field I want to copy data is the ContactCD I would only need to list all ContactCD's in the Contacts Table. Is that correct. This is a new database and I am a novice but learning quickly.

 

[sctb0825]

Can you show us your tables and relationships? (expanded so we can see all fields)

I will want to do the same thing for the AgentCD

 

[sctb0825]

I will want to do the same thing for the AgentCD

Wrong image here is the correct one, there are no other relationships created as of yet

 

[GPGeorge]

Does not work for me either, until I copy link address. I just stay on this page.
My issue probably due to Malwarebytes Browser Guard, as it asked me if I wanted to go to the site, when I pasted the link url.

Thanks. I am afraid I'm going to have to bite the bullet and install some sort of SSL certificate to satisfy the AV/MW screeners.

In the mean time.

 

[sctb0825]

Does not work for me either, until I copy link address. I just stay on this page.
My issue probably due to Malwarebytes Browser Guard, as it asked me if I wanted to go to the site, when I pasted the link url.

I was able to get it by copy link

 

[GPGeorge]

Wrong image here is the correct one, there are no other relationships created as of yetView attachment 105019

That's different from the original question. I assume "Carriers" is the real name, and that "customers" was an alias to "simplify" the question?

Given the fact that you have a many-to-many relationship, these two tables are not set up properly. But even before we get there, the screenshot reveals a significant oversight. There is no Primary Key defined for Carriers, although it appears that there is a "CarrierID" which one would assume is an AutoNumber. If so, that field, CarrierID should be designated the Primary Key for the table. If not, further explanation is needed.

Okay, then, the CarrierContact table is used to relate one or more carriers to one or more contacts. The relationship line drawn in the screenshot is on the wrong fields. It should be from the Primary Key of Carriers (again, presumably "CarrierID") and the same field, "CarrierID", in the CarrierContact table. There should be NO key for a Contact at all in the Carrier table, which one assumes the CACTCD is supposed to be.

The Contact table's Primary Key would be in the CarrierContact table as a foreign key, which appears to be CACTCD(?). And the relationship would be between those two fields for ContactID.

 

[GPGeorge]

I was able to get it by copy link

As noted, I am being pushed to implement some sort of security to satisfy the screeners, or give up offering demos and samples.

 

[Pat Hartman]

Here is an example of a m-m. It shows the relationships from both directions using two different methods. One way uses a subform. The other uses a popup. Both are correct but usually one will make more sense in any given situation.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk