Remove item from box when selected

[memnac]

OK, i have a form with 3 controls. 1 x textbox, and 2 combo boxes. The
scenario is like capturing students taking courses in a specified week number.
e.g.
TxtWeekNo CboCourse TxtStudentName
1 Engineering Joe Bloggs
1 Engineering Jay Rock
1 Law Mary Joe
2 Engineering <Nothing to select!>

What i want to do is upon selecting the course, i want to filter for students
not already specified for that week and course.
Referencing the above table, when i select "Engineering", i have two students
in the combo dropdown Joe & Jay, where i specify Joe in the first instance.
In the second record, for the same week and course, i only have one student
to select - Jay.
The query i have works well for week 1 BUT when i start with week two, if i
specify Engineering, i have NO MORE STUDENTS TO SELECT! I'm pretty sure there
is a way to refresh the box for new weeks. PLEASE HELP!!!

 

[Allen Browne]

The answer to your question will depend on the structure of your tables, but
you will probably need to use a subquery to identify the students who are in
the course but are not yet in the week.

If subqueries are new, take a look at:
http://allenbrowne.com/subquery-01.html#NotThere
Essentially your subquery will use:
WHERE NOT EXISTS (SELECT ...)
as in the first example on that webpage.

This also assumes that your main query gives you every combination of week +
course + enrolled student, so the subquery can eliminate those already
entered for that combination.

 

[memnac via AccessMonster.com]

Thanks Allen! Tried it and it works perfectly!

The answer to your question will depend on the structure of your tables, but
you will probably need to use a subquery to identify the students who are in
the course but are not yet in the week.

If subqueries are new, take a look at:
http://allenbrowne.com/subquery-01.html#NotThere
Essentially your subquery will use:
WHERE NOT EXISTS (SELECT ...)
as in the first example on that webpage.

This also assumes that your main query gives you every combination of week +
course + enrolled student, so the subquery can eliminate those already
entered for that combination.

OK, i have a form with 3 controls. 1 x textbox, and 2 combo boxes. The
scenario is like capturing students taking courses in a specified week

[quoted text clipped - 21 lines]

there
is a way to refresh the box for new weeks. PLEASE HELP!!!

 

[memnac via AccessMonster.com]

Thanks Allen! Tried it and it works perfectly!

The answer to your question will depend on the structure of your tables, but
you will probably need to use a subquery to identify the students who are in
the course but are not yet in the week.

If subqueries are new, take a look at:
http://allenbrowne.com/subquery-01.html#NotThere
Essentially your subquery will use:
WHERE NOT EXISTS (SELECT ...)
as in the first example on that webpage.

This also assumes that your main query gives you every combination of week +
course + enrolled student, so the subquery can eliminate those already
entered for that combination.

OK, i have a form with 3 controls. 1 x textbox, and 2 combo boxes. The
scenario is like capturing students taking courses in a specified week

[quoted text clipped - 21 lines]

there
is a way to refresh the box for new weeks. PLEASE HELP!!!