List population against another 1

[ZOR]

I have a listbox on a form that fills with names that have already been picked from a list and added to the record.

List 1 - Fills with names already put in record

mysql = "SELECT JUNCTION.AthleteID, JUNCTION.ID2, AthleteNames.Athlete,"
mysql = mysql & " AthleteNames.Country, AthleteNames.Other"
mysql = mysql & " FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID"
mysql = mysql & " WHERE (((JUNCTION.ID2)=[forms]![Courses]![sbfStudents].[Form].[ID2]));"
Me.List11.RowSource = mysql

However if a user wants to add new names from a list, is there a way I can EXCLUDE the names from the list that are already in the record, ie to avoid duplication

List 2 - Provides a list of available names to pick from to add in record
SELECT AthleteNames.Athlete
FROM AthleteNames
ORDER BY AthleteNames.Athlete;

Sorry for a sticky question. Thanks

 

[lespaul]

if this is on the same form then something like this would work (depending on the number of records, it may take a while to run)

SELECT AthleteNames.Athlete
FROM AthleteNames WHERE AthleteName NOT IN 
(SELECT AthleteNames.Athlete FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID WHERE JUNCTION.ID2=[forms]![Courses]![sbfStudents].[Form].[ID2])
ORDER BY AthleteNames.Athlete;

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ZOR]

Many thanks. I changed the line WHERE AthleteName NOT IN to be Athlete, and it works perfectly, and not slow. Excellent, have a star and thanks again.