Hello guys,
I have been reading various articles from this forum about the subject.
I found one very good, that teaches how to use multi drop down list to populate a data.
However, what I had found was the use of a drop-down list with a unique table. When I will try to use with more than one table, it not works.
In the example (file attached - this file I took from one of the examples that I found here in this forum, and I modified then.) I had this situation: Table 1, table 2 and table1/2 (table 1/2 is responsible to create the many to many relationships between table 1 and 2).
When I use only table 1 (form 1) everything is fine. The first dropdown list show numbers 1 to 3. When push button (refresh form), dropdown list 2 show numbers 101 to 103. When button 2 pushed (refresh form), linked names appear in the table control to be populated.
However, when I tried to link the multiple tables in form 2, it is not working.
In this scenario, dropdown list 1 should be related to table 1, dropdown list 2 related to table 2 and the table control need to be populated. The question is: table control must to return data from table 1 (names) and allow to be populated with the blank fields. I don’t know how to do it. Thank you Template.odb
Hello,
Your approach is incorrect. In a many-to-many situation you have table 1 and table 3 with the criteria to be linked. Table 2 would be the table which contains the connection between the two tables. This table only has three fields: ID (unique number); link to table 1 ID; link to table 3 ID. You cannot fill table 2 fields from the two list boxes on a normal form unless you write your own macros. Instead, with table 2 as a table control, make the columns list boxes based on criteria tables.
For a sample of this (albeit more elaborate) see this post → Complex forms with data and data entry from many to many relationship tables. There in my answer is the Plasmids.odb sample. On “Test Form” notice the top three table controls: Master; Link; Feature Detail…
In the “Link” control, “PlasmidsID” and “FeatureID” are list boxes. This is how to create the relationship.
Edit 2018-05-10:
@VHM You have now changed requirements. By adding select ALL in a term or SPECIFIC with a class would require specific macro coding dealing in the area of links between the Form & Sub Form. Even if code were written, a small change on your part would possibly require the code to be changed also. This is not good.
You have other problems also. You cannot have a sub form that you want to update if the fields are not the same as the table record fields. You did this when you created a column concatenating the first & last name of the students. You also have duplication of information. For example, student info should be in its’ own table. Your original question stated a many-to-many link. This is not the case. All links here are one-to-many.
There are too many unanswered questions to present something which is ready-to-use. However, attached is a sample. The sample uses a table filter to select records based upon the list boxes. It also better breaks down information by separating it into their proper tables. Again so many questions that even this may be better broken down further. The Selection form is what this is aimed at. Spring 2017 & 101-Algebra will give results as will Autumn 2017 & 307-Python.
It may help for you to look through the LO documentation for Base. It has a getting started guide, samples and much about designing a DB. You can find it here → Documentation/Publications
Sample - Registered.odb
If this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
@VHM It would also help if you would specify just what you are attempting. Be as specific as possible. From the fields in the sample provided it is confusing. Results may not be what you are looking for.
Thank you Ratslinger. I will check your answer and as soon as possible I reply here.
Thank you,
Hi @Ratslinger. Thank you for your help. I looked for the file that you mention above, but being more specific about my issue:
The user needs to populate the control table accordingly with the filters results. So, table number 1 is the intake or terms (Summer17, Spring17, Winter17, Summer18, Winter18, etc.). Table 2 are the classes (Class 1, Class 2, Class 3, etc.). So, the first filter selected shows the classes accordingly with the term.
For example. If Spring17 was selected, shows classes 3 and 4. If class 3 was selected, show, in the table control a list of students that are studying in class 3, spring 17. So, for each student listed, it is necessary to add information (for example, grade). If spring18 was selected, shows classes 1 and 2 and 3, for example. If class 3 was selected, show other students studying in the class 3, spring18 and for each student, some information is added.
The problem is, I cannot use the filters and show, in the table control, the list of students and, at the same time, populate it. When I use just one table, (Form 1), it is ok. But when I try to use more than 1 table, I cannot establish a link between then to allow populate the table control.
Thank you @Rastlinger. The sample that you provided will help a lot. It is exactly what you did in the sample that I am looking for. Now I will review what I did to adequate it. Thank you!!