Hi!
I have those (simplified) tables that are linked with an n:m relation
persons:
#ID#FullName#
trainings:
#ID#startDate#
training_members:
#ID#trainingID#personID
Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:
I also added "me.recalc" to the after_update event of the dropdown list.
It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.
Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...
Thank you in advance!
I have those (simplified) tables that are linked with an n:m relation
persons:
#ID#FullName#
trainings:
#ID#startDate#
training_members:
#ID#trainingID#personID
Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:
Code:
SELECT persons.ID, persons.FullName FROM persons WHERE persons.ID Not In (SELECT personID from training_members inner join trainings on training_members.trainingID = trainingsn.ID) ORDER BY persons.FullName;
I also added "me.recalc" to the after_update event of the dropdown list.
It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.
Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...
Thank you in advance!