Populating Junction table from form

[JFo]

I am trying to create a main form that selects a series of values allowing the user to enter new data or edit current data (using other forms). I have a series of cascading combo boxes that populate accordingly and become visible after update. I can successfully create the series and add new data and edit data as in my code.



I am wondering how to populate the junction table from the forms so for example when editing or adding a new site it not only populates the tblSites and the tblSitesContacts it also populates the tblClientSitesJunction with values from the frmSites and also cmbClients. If I use the wizard and try and add values from all 3 tables to form it doesn't work and I am not sure how to add the ClientID and SiteID manually to frmSites.

I have attached a copy of my database to help with understanding and it would be great if someone can point me in right direction and please do not hesitate to ask if anything is not clear

Thanks

Attachment 4334

[June7]

CANNOT add record to junction table and record into clients and sites from the same form because of the many-to-many relationship. Records must exist in clients and sites for them to be available for selection in junction record. If record does not yet exist then open appropriate form, add record, return to junction form, refresh the form so combobox RowSource will update and select the newly added record.

[JFo]

Don't understand can this all be done at back-end I can't have my users going between forms refreshing. I just want users to be able to go in one direction? What does this mean I have to make a junction form as well?

Ideally I just want one main menu form where they select from options and only veer off main form to either edit or add new instance of for example client or site (once a new client/ site added it then goes back to main form). Eventually there will be about 10-20 combo boxes on this main form and if all options are selected then they can print reports etc from database

Not sure if this is correct way to do this then or if access is even the correct database to be using as I want the flow to be seamless and idiot-proof

Thanks again for your assistance June7

[June7]

Ideally I just want one main menu form where they select from options and only veer off main form to either edit or add new instance of for example client or site (once a new client/ site added it then goes back to main form). Eventually there will be about 10-20 combo boxes on this main form and if all options are selected then they can print reports etc from database

What options and comboboxes do you mean? At present your MainMenu form has only 3 comboboxes and options to add/edit clients, sites, plants but not an option to enter a record to junction table that will associate a site with a client or a plant with a site.

Keep in mind that the more 'seamless and idiot-proof' the more code is needed. I think you are on the right track.

[JFo]

Yes I have removed other combo boxes for ease of figuring out exactly what I want to do. Plants will be the next combo box and there will be a number after that. I was hoping to not give user any option to associate client with site I wanted this to be automated, hence the combo box is hidden before a client is selected. However this will not work either as if I have not assigned a site to a client then the site will not be available and if they add another than there will be 2 instances of same site.

Essentially I users will either cycle through entering everything or select form list of options until they find the top of tree they are searching for and print reports

Obviously the difficulty therein lies in populating all fields in all tables whilst getting the user to do as little as possible

I am open to suggestions

Cheers

[June7]

A record must exist in the ClientSite junction table in order for there to be an association. How do you want these records created? Need to have some form to serve this purpose.

Set the ClientID and SiteID fields as compound primary key in the junction table and this will prevent duplicate associations.

[JFo]

I understand this record needs to exist in the junction table to link them I was hoping that by selecting a specific value from client combo box then the dual IDs could be automatically updated when a site was entered. However this still does not stop the problem of then getting multiple sites that are the same

My difficulty is that
- Every client has a main contact (this is in Client table)
- many clients can be on 1 site and 1 client could be on many sites (hence junction)
- 1 site may have many client contacts (hence clientsitecontact table)

however the user that enters data will not be wanted to think about refreshing, going backwards or anything of the sort I need to order the data entry and retrieval in a way that it is logical and flows from start to finish

I figured that I would get the first 2 combo boxes/ tables finished with data enrty and retrieval before i think about adding the rest of the database

[June7]

Back to your original post

I am wondering how to populate the junction table from the forms so for example when editing or adding a new site it not only populates the tblSites and the tblSitesContacts it also populates the tblClientSitesJunction with values from the frmSites and also cmbClients.

I tried to address your question about populating the junction tables. As presently structured, your db will not accomplish this. To do anything near what you describe will require code, probably using sql INSERT action statement. When you are ready to tackle that, come back.

[JFo]

I am ready to tackle that now.

I want the form or similar to populate all tables at once in the easiest way I thought if I had the clientid passed from client combo box and then added new site it could still take the clientid and new siteid and put them both in the junction table

maybe an idea would be to have editing mode and searching mode before going into main form and that would solve my problem of adding multiple sites for example

[JFo]

I think I have an idea however I don't know how to do it. I am thinking of having a subform in the Sites form that has clientID and SiteID from junction table and when edit site or add site is selected then the value of clientid from first combo box is passed to this subform. Finally when the save button is pressed on site form this should then update site, clientsitejunction and clientsitecontact tables. now the question is how do i pass the clientID value across? Can I do it with text box because I can't find a row source property in text box?

[June7]

Do not recommend this approach. Because Clients/Sites are many-to-many relationship, the junction table should be on a solo form and comboboxes for selecting client and site on the form. Also any other fields for the junction table you want to include, such as DateEnter, EnterBy, etc.). Clients and Sites cannot be linked subforms. If client or site does not exist, then code would open the appropriate form for new entry, save record, refresh the comboboxes and populate the combobox with the new ID. This is all behavior I have accomplished by using LimitToList property, OnNotInList event, BeforeUpdate event. It can be somewhat complicated.

[JFo]

Yes sounds a bit complicated and I don't totally understand it

I don't suppose you have an example of this in action just 3 tables (2 separate tables and a junction table) and form(s) that populates/ extracts data from all these 3 tables, I feel if I can do this then it is a major step for my database to evolve faster? or is there any examples of this already working as required on-line somewhere?

Cheers you have devoted a lot of time answering my questions and I am grateful

[June7]

I don't have a simple project to demo this. Give me a couple days and I might have one built, probably based on your structure.

I am sure there are a lot of template projects out there that incorporate this concept but since I've never taken advantage of any, can't direct you to a particular one.

[JFo]

Thanks for your help I have been searching on-line however cannot find one, let me know if anything comes to mind or you come across anything?

[June7]

Attached is project with coding for you to explore. Demos two approaches for data entry.

Note the revised RowSource for cmbSites. What you had didn't make sense. It restricted selection of SiteIDs to values already associated with ClientID in the junction table. This would result in duplicate pairs, a violation of the compound primary key.

I didn't deal with Plants and the ClientSiteContacts because I don't understand the relationships of this data to Clients and Sites. Not sure really know how clients and sites relate. You said same client can have many sites and same site can have many clients? What are these 'sites'? What are 'plants'?