Populating Junction table from form

[JFo]

Thanks June7 it is above and beyond my expectations that you have gone to so much trouble to help me. I will try and work my way through this and see if I can implement some of this technique for my project.

I think I can utilise this for at least adding a client to a site and updating junction table in process... maybe can add this asa subform to my frmSites

Thanks again

[June7]

Okay good luck.

[JFo]

Yes it is one extra link in chain however it works and I can just get the users to add client to site if a site does not appear in filtered combo.

Thanks buddy

[JFo]

OK now I have come to similar problem with Clients/ Sites/ Contacts. I can have Contact in subform with Sites and it therefore links to sites however now i cannot link it to client as well.

Essentially relationship is every client has one major contact - management at head office
(this part is easy as can be stored in client table)

Then each client can be at number of sites and each site can have a number of clients (we have sorted this situation with massive thanks to June7).

Now the next issue in this sequence is there must be a specific client site contact for each client that is on a specific site of which relationships exist;
- 1 site may have multiple clients and each client must have a contact specific to each site
- 1 client may serve multiple sites and therefore must have a specific contact for each site


As a result of this My ClientSiteContact table has been set up with foreign keys of both the clientID and the SiteID of the corresponding tables together with a unique clientsitecontactid. This is all very well and I think the relationship is correct however this also leads me to a similar problem...

How do I now input this data? How do I get the clientID and SiteID into this table so that when the ClientSiteContactID is requested it will accurately return the specific contact for the specific client at the specific site?

Thinking maybe a seperate form such as Clientsitejunction form and getting user to link contact or maybe i can use the dual primary key values that already exist in clientSitesJunction table to somehow simply link the clientsitecontact to?

Are these valid ideas? any input as usual is much appreciated, can't believe how many brick walls I seem to be hitting

thank you