Need help with my Many-to-Many relationship db

[tobydobo]

Just created a Many-to-Many relationship db but have no clue how to implement it into my forms. I have attached screen shots and a copy of my project template. Here is a summary of what I want to do:


1. As instructed by the Many-to-Many relationship db tutorial I watched, I have created 3 tables: Techs, Site, and ProjectJunction. I am doing it this way because some of the same techs work at multiple sites.

2. using MS's Contacts' template, I have modifed the contact list to 'Site List' as the default form, and set it to datasheet view. The Site List will display all the sites (from site table) I support. Each site record will contain information specific to a site (i.e, managers, addresses, site contacts, etc).

3. Next I want the link in the 'open' column to open the 'Site Details' subform. this part doesnt work quite right on my attached project but I know how to correct this part.

4. On the Site Details subform, I have enabled split form view with the site details at the top of the form and the datasheet portion at the bottom.

5. Part I need help with: In the datasheet portion of the Site Details subform, I want to be able to list the assigned techs for that site (pulled from the tech table). Not quite sure how to do that part as the datasheet portion currently only displays the Site List information. Im assuming I need to first link the techs to the site first in the table, but after that Im lost.

6. Part I need help with: Once I get the techs to show up at the bottom of the Site Details subform, I want to be able to click an open link (the same as I do for the Site List form), which will open my 'Tech Details' subform.

Thats about it....hope I explained everything ok. Any help would greatly be appreciated.

[dblife]

Hi, I have put the attached bits and pieces together to illustrate how to use junction subforms on main forms and how to link to them from other subform.
You select the site and the value underneath changes.
The subform on the right is looking for the value in this text box to 'know' which site to filter for and reflects the change straight away.
Let me know if you need further explanation.

[June7]

First, TechID in Projectjunction should not be a primary key, should be Indexed Yes (Duplicates OK).

You can show the related techs on Site Details with a subform, listbox, or combobox in the Header or Detail section, not in the datasheet part of split form. The RecordSource or RowSource would be:
SELECT Projectjunction.TECHID, Techs.[Tech Name], Projectjunction.SITEID FROM Techs RIGHT JOIN Projectjunction ON Techs.TECHID = Projectjunction.TECHID;

If you do combo or list box, add on: WHERE (((Projectjunction.SITEID)=[Forms]![Site Details (beta)]![SITEID]));

[tobydobo]

Wow! thanks dblife and June7!! I think I may try to implement a little of both of the ideas you presented. I really like dblife's idea though of having both lists on the main form. I would need to be able to launch a seperate subform when I click an item from the list so i can see the individual record. Is that a macro that does that?

[June7]

Macro or VBA code. I use only VBA. Search Access Help on topic: Access Programming.

[dblife]

You could use the double click event to open a new form filtered for the record you need.. Access has a wizard to help you do this and will write the vb for you..

Wow! thanks dblife and June7!! I think I may try to implement a little of both of the ideas you presented. I really like dblife's idea though of having both lists on the main form. I would need to be able to launch a seperate subform when I click an item from the list so i can see the individual record. Is that a macro that does that?