Gathering Data from Two Tables to a Connecting Table

[Huddle]

I have two tables: "Tbl_CM_Project_Details" and "Tbl_CM_Inventory" that have the data that I am trying to connect with a third table: Tbl_CM_Proj_CMI_Connector.

Tbl_CM_Project_Details connecting field is PK_Project_Num to Tbl_CM_Proj_CMI_Connector field Connecting_Project_Num
Tbl_CM_Inventory connecting field is CMI_ID to Tbl_CM_Proj_CMI_Connector field Connecting_CMI_ID

On the form I have a SQL Query where I enter the PK_Project_Num and the CMI_ID Auto enters. What I need is for this information to automatically fill the connecting table so that the information is connected.

Any suggestions on how to accomplish this?

[orange]

Can you tell us in plain English what you are trying to accomplish?
Do you have a many to many relationship between "Tbl_CM_Project_Details" and "Tbl_CM_Inventory"?

[Huddle]

I need PK_Project_Num to automatically fill Connecting _Project_Num when I enter PK_Project_Num into the form. And I need CMI_ID to automatically fill Connecting_CMI_ID when I the CMI_ID auto enters so that the data between the two table can be connected by the third without me having to go into the third table and enter the information.

[orange]

How do you think Access or any software would know these values?

Consider an analagous situation with Students and Courses

Student<---->Course (many to many)

To resolve the situation

Student--->StudentEnrolledInCourse<---Course

tblStudent
StudentId PK
StudentName
other Student info

tblCourse
CourseId
CourseName
CourseDsecription
other course info


tblStudentEnrolledInCourse
StudentEnrolledInCourseId PK

StudentId (these make up a unique compound index(to prevent duplicates)
CourseId
other fields specific to this combination such as Grade

[Huddle]

I thought there might be a way that when I entered PK_Project_Num on the form Connecting_Project_Num could be auto filled on the form using some type of ME. statement. Or that the Connecting_CMI_ID could default to the CMI_ID. There is usually more than one way to skin a cat but if it's not doable I can accept that.

[orange]

I don't think there is any method to do what you are asking. If you look at the Students/Courses analogy, you(user) have to assign a Student to a Course.

There is a video here that goes through this common process
https://www.youtube.com/watch?v=7XstSSyG8fw