Hide data in combo box that has been previously selected

[LucyCC]

Based on your query, it looks like the PK to the lookup table is an ID rather than the visible value. If you remove used items, the combo on existing rows will go blank on a continuous form.

Why do you want to remove the already used items? If you want to prevent them from being used, do that with code in the BeforeUpdate event of the combo.

This is a really old thread but I'm having the same issue and am confronted with the problem you describe, i.e. if I remove the items from the rowsource then all the existing rows have gone blank. I know I can prevent people from re-using existing selections with code in Before Update as you say but what I'd really like is to remove the options already selected. Do you have any thoughts on that?

 

[CJ_London]

recommend you don't hijack an existing thread -- start your own

 

[Pat Hartman]

Welcome aboard

Here is one way to fix them. It only works in single or continuous forms. There is another technique that works with forms in DS view but I don't have a link.

Cascading Combos in continuous form

This is an example that shows how to work with cascading combos in a continuous form. This technique will NOT work for a form in datasheet view. Here's an update.

www.access-programmers.co.uk

As I mentioned in the post you quoted, my preference is to add an ActiveYN field to the lookup table. That lets you sort the inactive items to the bottom of the list to keep them out of sight but it also requires code that prevents the inactive items from being selected for new or existing records. They stay on old records but no new instances can be added.

Also, as CJ mentioned. it is always best to start your own thread. Old threads tend to get long and most people are not willing to read the entire thread and so will ignore your new question. If the old thread has some relevant information, you can add a link to it and mention where it is.

 

[CJ_London]

This link allows cascading combo’s to work in a datasheet

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk