Need help with SQL Query for Row Source

thewilli

New member
Local time
Today, 14:01
Joined
Feb 5, 2008
Messages
7
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:
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!
 
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:
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!

I would try this as the row source, but I'd like to see your form and data.

Code:
SELECT persons.ID, persons.FullName 
FROM persons 
WHERE persons.ID Not In 
(SELECT personID from training_members ) 
ORDER BY persons.FullName;
 

Users who are viewing this thread

Back
Top Bottom