469,936 Members | 2,422 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

dynamically removing entries from a list

Hi all,
I've bound cbo field in a form, which draws from a list, with its 'limit to
list' property set to true.When i add an entry to the form, from the list,
I want to remove this item from the list, so the user does not get to pick
this item for the next record. I know this is not a trivial matter (for me),
but would be interested what approach others would take.

The application is a training one, where a training course is planned, then
trainees are added, from a list. I wish to exclude trainees from this list,
if they are already booked onto a course for that date. The training course,
course instance (course date) and training (who is on the course) are all
subforms, of a form bound to the courses table.

Any advice/ideas most welcome

Gerry Abbott


Nov 13 '05 #1
4 1374
"Gerry Abbott" <pl****@ask.ie> wrote in message news:<zv*******************@news.indigo.ie>...
Hi all,
I've bound cbo field in a form, which draws from a list, with its 'limit to
list' property set to true.When i add an entry to the form, from the list,
I want to remove this item from the list, so the user does not get to pick
this item for the next record. I know this is not a trivial matter (for me),
but would be interested what approach others would take.

The application is a training one, where a training course is planned, then
trainees are added, from a list. I wish to exclude trainees from this list,
if they are already booked onto a course for that date. The training course,
course instance (course date) and training (who is on the course) are all
subforms, of a form bound to the courses table.

Any advice/ideas most welcome

Gerry Abbott


So you're "using up" the entries in the combobox as you go, right? If
I remember right, you just do something simple.

SELECT ...
FROM tblSourceList LEFT JOIN tblDestinationList ON
tblSourceList.ItemID=tblDestinationList.ItemID
WHERE tblDestinationList.ItemID IS NULL

If I understand the question right, you just set the rowsource for the
combo to a simple left join query that does the filtering for you.

So in your case, you might OR or UNION your two sets together so you
can knock out a bunch of folks that couldn't attend the training
anyway.

Sorry, the senility...

it's either an IN/NOT IN subselect or a left join.
Nov 13 '05 #2
Gerry Abbott wrote:
Hi all,
I've bound cbo field in a form, which draws from a list, with its 'limit to
list' property set to true.When i add an entry to the form, from the list,
I want to remove this item from the list, so the user does not get to pick
this item for the next record. I know this is not a trivial matter (for me),
but would be interested what approach others would take.

The application is a training one, where a training course is planned, then
trainees are added, from a list. I wish to exclude trainees from this list,
if they are already booked onto a course for that date. The training course,
course instance (course date) and training (who is on the course) are all
subforms, of a form bound to the courses table.


you could include a Selected field in the table (type Boolean) and base
the left list on WHERE NOT Selected and the right list on WHERE Selected

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #3
Hi Pieter,
I worked out a query (like you described) which gave me the of participants
who had attended, then joined it with the full list and like you pointed
out, just filtered out the null values for the foreign key field.

Since I had to do some work on getting the list participants who already
attended, without duplicates, I created a second query, and joined this with
the list of trainees.

I would like to konw how to combine such queries into a single one, but that
is for another post.

Many thanks.

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:<zv*******************@news.indigo.ie>...
Hi all,
I've bound cbo field in a form, which draws from a list, with its 'limit
to
list' property set to true.When i add an entry to the form, from the
list,
I want to remove this item from the list, so the user does not get to
pick
this item for the next record. I know this is not a trivial matter (for
me),
but would be interested what approach others would take.

The application is a training one, where a training course is planned,
then
trainees are added, from a list. I wish to exclude trainees from this
list,
if they are already booked onto a course for that date. The training
course,
course instance (course date) and training (who is on the course) are all
subforms, of a form bound to the courses table.

Any advice/ideas most welcome

Gerry Abbott


So you're "using up" the entries in the combobox as you go, right? If
I remember right, you just do something simple.

SELECT ...
FROM tblSourceList LEFT JOIN tblDestinationList ON
tblSourceList.ItemID=tblDestinationList.ItemID
WHERE tblDestinationList.ItemID IS NULL

If I understand the question right, you just set the rowsource for the
combo to a simple left join query that does the filtering for you.

So in your case, you might OR or UNION your two sets together so you
can knock out a bunch of folks that couldn't attend the training
anyway.

Sorry, the senility...

it's either an IN/NOT IN subselect or a left join.

Nov 13 '05 #4
"Gerry Abbott" <pl****@ask.ie> wrote in message news:<6h*******************@news.indigo.ie>...
Hi Pieter,
I worked out a query (like you described) which gave me the of participants
who had attended, then joined it with the full list and like you pointed
out, just filtered out the null values for the foreign key field.

Since I had to do some work on getting the list participants who already
attended, without duplicates, I created a second query, and joined this with
the list of trainees.

I would like to konw how to combine such queries into a single one, but that
is for another post.

Many thanks.


I would assume you could do two queries and then just use a UNION to
return the unique values, right? Only problem is that if your queries
are really long, you could run out of space. So try the copy/paste
thing!
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Amit | last post: by
reply views Thread by sameer mowade via .NET 247 | last post: by
4 posts views Thread by sri2097 | last post: by
4 posts views Thread by =?Utf-8?B?T2xhbg==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.