Dropdown list that only displays options that are still available

[Nikki]

I've been searching for a while on this, but I haven't found the right
keywords to get to my answer.

I'd like to create a Lookup field that only displays values that have
not been selected in previous records for this same field in this same
table. For instance, if I'm assigning pieces of candy to children,
but I have only 1 of each flavor, once I select grape in the first
child's record, I don't want grape to display as an option in the drop
down anymore for future records. If that can't be accomplished in the
table itself, but a form, that's fine too of course.

I'm using Access 2007 and believe it or not, I'm fairly comfortable
with Access and even somewhat comfortable with VB and SQL. My brain
refuses to wrap around this one though.

 

[Jeff Boyce]

Nikki

You could follow the suggestion else-thread, but then you'd need to be
constantly "maintaining" your table to indicate (yes/no) which item(s) had
been selected.

An alternate approach would be to build a query that finds all items in your
"available" table that are NOT already used in your "what folks picked"
table.

If you launch the query wizard, one option is the "unmatched" query. I
suspect you want to know which "available" items are not matched (i.e., not
in) your "picked" table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nikki]

I am able to create an unmatched query between the options available
and the options selected in records in the childcandyassignment
table. However, here is where my thinking is probably flawed. I go
back to the design of the childcandyassignment table and use the
lookup wizard to refer to the unmatch query, however I got a message
saying there are no valid fields because available fields refer to the
same table. How should I indicate that options should come from this
unmatched query which refers to the same table?

I appreciate the help and even though I'm being dense and my example
is silly.

 

[Jeff Boyce]

Create a query against your childcandyassignment table that returns the
"assigned candy" items. Make it a unique query, so you only see each used
item once.

Create a query against your availablecandy table that returns all possible
candy items.

Create one more query, using the query wizard to build an unmatched query
(items in availablecandy not in the query of items used in
childcandyassignment.

Use that third query to feed your combobox.

I'm not clear on what "lookup wizard" you are referring to... are you using
a lookup datatype in one of your tables? If so, stop now!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I guess I'm just not as good as you at reading stuff that wasn't there.

I re-read your response and didn't see any mention of the AfterUpdate event.
If by "interpret the written word" you mean "jump to conclusions without
sufficient explicit information", I plead guilty!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Didn't you know that's how Steve can offer to work for so little money? He
intuitively understands the OP's needs, even if these were expressed in just
a sentence or two.

 

[Hans Up]

I'd like to create a Lookup field that only displays values that have
not been selected in previous records for this same field in this same
table. For instance, if I'm assigning pieces of candy to children,
but I have only 1 of each flavor, once I select grape in the first
child's record, I don't want grape to display as an option in the drop
down anymore for future records. If that can't be accomplished in the
table itself, but a form, that's fine too of course.

Use a combobox or listbox control on a form to do this. (Please heed
Jeff's advice, and don't attempt to use a table lookup field for this
purpose.)

You can use a query as the RowSource for your control.

SELECT a.candy_name
FROM candies AS a
LEFT OUTER JOIN childcandyassignment AS b
ON a.candy_id = b.candy_id
WHERE b.candy_id Is Null
ORDER BY a.candy_name;

In your form's "on current" event, requery the control so that the list
of remaining (unassigned) candy choices is updated before you make a
selection for the current record.

Good luck,
Hans

 

[Nikki]

Thank you so much everyone for your help. It's making sense now.
Sometimes I have a hard time making the leap from books and training
to what I'm trying to do.

I wasn't aware that using the table lookup field was a bad idea. I'll
have to look that up so I can understand why that is, but I'll take
everyone's word for now. ^.^

Thanks again!

 

[Hans Up]

I wasn't aware that using the table lookup field was a bad idea. I'll
have to look that up

That was pretty darn funny, Nikki!

so I can understand why that is, but I'll take
everyone's word for now. ^.^

http://www.mvps.org/access/lookupfields.htm

Lookup fields are The Evil Way ... there be monsters and other scary
stuff down that path!

 

[Jeff Boyce]

That's where you made your mistake, Steve. The folks who post and respond
here are not "customers"!

Jeff Boyce

 

[John... Visio MVP]

You misspelled Victim.

Why not get an honest job and go back to pumping gas? What is so
hypocritical is that in the past, you have claimed to teach Sunday school,
but you make your money conning people. If you think your skills are worthy,
then put something on your website or blog and go to a legitimate job board
and post there. Or can you not compete in that sort of an enviroment.

John.. Visio MVP

 

[BruceM]

There is no evidence whatever to suggest you have a successful business
providing Access support. Your word alone is unconvincing at best.