Add New Record (1 Viewer)

[Randomblink]

Ok...

I have Three tables, I am going to give an example and hope someone can help.


Table #1: [tbl_Employee]
Table #2: [tbl_LINK_EmployeeProject]
Table #3: [tbl_Project]

Ok, in Table #1, I have three fields.
#1) Employee_ID (This is an autonumber field, primary key)
#2) Employee_Name
#3) Employee_Department

Ok, in Table #2, I have two fields.
#1) Employee_ID (This is an Integer field, primary key)
#2) Project_ID (This is an Integer field, primary key)
In Table #2, I set them both as Primary keys by highlighting both fields and clicking on the key icon in Access.

Ok, in Table #3, I have many fields, but only two are important.
#1) Project_ID (This is an autonumber field, primary key)
#2) Project_Title

I have gone into the relationship area and linked the following fields.

[tbl_Project].[Project_ID] connected-to [tbl_LINK_EmployeeProject].[Project_ID]
AND
[tbl_Employee].[Employee_ID] connected-to [tbl_LINK_EmployeeProject].[Employee_ID]

Ok...
I create a form to input new Projects. What I want to do is:

I have a drop-down/combo-box: [cmb_AssignEmployee] that looks up values from [tbl_Employee]. What I want to do is select an employee with this box, upon selecting an employee it will then create a new record in [tbl_LINK_EmployeeProject] where the [tbl_LINK_EmployeeProject].[Project_ID] field will equal the [tbl_Project].[Project_ID] AND where the [tbl_LINK_EmployeeProject].[Employee_ID] field is equal to the selection I just made from the combo box.

Does this make sense?

Basically, I want to link several Employees to each Project. Several Employees can be on ONE Project AND several Projects can be handled by ONE Employee.

Other than manually inputting directly into the table I don't know what to do. And the BIGGEST problem is, this Project is linked the same way to many other tables in the exact same way.

I am fairly accomplished / self-taught in VB. I can create reports, forms, and modules fairly well. But I have never dealt with Recordsets which is what I believe I need to understand to make this work. So, point me in the right direction...please!

 

[Sohaila Taravati]

It seems to me that you need to create a many to many relationship between your tables.

Sohaila

 

[Randomblink]

Many-to-Many

I did create a Many-to-Many relationship by creating the LINK table.

Now I need to know how to drop a new record into the LINK table carrying the Project_ID from the currently open Project and the Employee_ID from the drop-down/combo-box as the two entries.

Can someone help?

I am searching and digging into OpenRecordset and AddNew, but hitting dead ends.

I should pre-face this whole thing with something that was JUST brought to my attention that it could be important.

This is a Front End database, so it holds NO DATA, it ONLY has linked tables.

Does this help make it more difficult? Sure feels like it to me.

 

[David R]

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=29694 may have an answer you are looking for.

 

[David R]

Your structure sounds correct. Your forms may be confusing you though. Let me resummarize from the other posting, since it might not have been as clear as I'd hoped:

You'll need to decide whether you want the Projects in the main form, or the Employees. You can build one each way, but based on your usage you probably know which way you usually assign things - do you go into the Employee record and add Projects, or do you go into a new Project and add Employees? Since your first posting used the latter I'll work with that possibility.

Your main form will be based on tbl_Project. Within that you'll have a subform. The subform is based on a query, joining tbl_Employee with tbl_LINK_EmployeeProject. Make sure you include the Project_ID field from the LINK table, not tbl_Project.

When you insert the subform make sure the wizard selects to only show those records where Project_ID from the subform matches Project_ID from the main form. Everything should be kosher from there on out.