Forms / Subforms and Linked tables for a Foundation

[Randomblink]

Ok...

I am going to give an explanation as easily as I know how hoping that someone can understand what I am asking as easily as they can explain what to do...

I have three tables.

tbl_ProjectBasics
tbl_LINK_ProjectBasics-Consultant
tbl_Consultant

tbl_ProjectBasics is linked to tbl_LINK_ProjectBasics-Consultant by a shared field [Proj_ID] they are also related in the Relationships window.
tbl_Consultant is linked to tbl_LINK_ProjectBasics-Consultant by a shared field [Cons_ID] they are also related in the Relationships window.

[Proj_ID] is an autonumbered field in the tbl_ProjectBasics and it is a Long Integer in the tbl_LINK_ProjectBasics-Consultant
[Cons_ID] is an autonumbered field in the tbl_Consultant and it is a Long Integer in the tbl_LINK_ProjectBasics-Consultant

Ok....

Now then...
One project can have several Consultants AND One Consultant can have several Consultants.

So, if I were to put a [Proj_ID] field in the tbl_Consultant then I couldn't give a Consultant more than one project. AND if I were to put a [Cons_ID] field in the tbl_ProjectBasics then I couldn't give a Project more than one consultant.

So, I create the three-table setup as recommended.

This allows me to have One Consultant with multiple listings in the tbl_LINK_ProjectBasics-Consultant and each listing can have a different Project...and vice versa...thereby allowing me to link one consultant to several projects...and...vice versa...

Ok...

So this is my set up...

I create a form for interacting with ProjectBasics
I create a subform for interacting with Consultants

What I want to do is list out all the Consultants in the subform that are connected with the Project currently loaded into the main form...

I have already set Master and Child to [Proj_ID]...

Now then...

How can I show this?

I want to be able to select Project XYZ and see that Consultant Joe, Sam, AND Mike are all part of that project...Instead I am limited to the first one that appears...If I put next record buttons on the subform it moves to the next record in the main form...so trying to view the next Consultant...pulls up the next project instead...

Argh! What can I do...?

 

[RichMorrison]

First things first. This is NOT a form/subform application. This is a cross-referrence application.

Since you have a MANY-To-MANY relationship between Projects and Consultants you need a form that shows all the Consultants assigned to a Project and all Projects on which a Consultant works. For the sake of clarity, you probably want 2 forms.

Your table structure is correct.

On one imaginary form you want:
1) a listbox of Projects on the left side. This list will be based on a simple query that looks the Projects table.
2) a listbox of Assigned Consultants on the right side. This list will be based on a query against the "Link" table. The query will have a criteria that refers to the Projects list.

When you click on a Project in the listbox, the Assigned Consultants list will be updated.

The second form is a mirror image of the first:
1) on the left side, a list of Consultants. The list in based on the Consultant table.
2) on the right side, a list of Projects. Based on another query against the "Link" table. The query will have a criteria that refers to the Consultant list.

This is just an outline. I am confident that you can fill in the details.

RichM

 

[Randomblink]

SQL From a Listbox

Private Sub lst_AssignContractor_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tbl_LINK_ProjectBasics-Contractor] ( Proj_ID, Cont_ID) " & "VALUES ([Forms]![frm_ProjectBasics]![Proj_ID],[Forms]![frm_ProjectBasics]![lst_AssignContractor]);"
DoCmd.SetWarnings True
cde_RefreshForm
Call Vizzy(lst_AssignContractor, "Invisible", Me!subfrm_ContractorBasics)
End Sub

As the user selects a Contractor from the listbox,
The listbox turns off the Warnings...
Grabs the Project_ID from the current project open...
Grabs the Contracts_ID from the listbox...
Creates an entry in the tbl_LINK_ProjectBasics-Contractor...
(using those two ID fields)
And then refreshes the form and makes the listbox invisible...

On a side note, I created another listbox that lists out all Contractors assigned to the project...
AS you click on a Contractor in this listbox, it REMOVES that entry in the tbl_LINK and refreshes the form....

This is a beautiful thing...