Unique Values to ComboBox (1 Viewer)

[dazza61]

Hi guys,

I've tried searching all the threads for an answer to my problem and I can't seem to find it....

Ok I know this should be simple...

I know to use Distinct and DistinctRow to only show unique records in a combobox but all I want to do is that once a selection is chosen in the combobox to add a record to a subform, it can't be selected again.

I know in some cases you do, e.g. items on an invoice sometimes need to be entered more than once, but I'm listing Skills for Employees and I only want a user to enter a Skill once per Employee (although Employees can have many Skills, I just don't want a User to accidentally put the same Skill in twice or more per Employee...)...

What's the best optimized way of achieving this?

Sorry for my newbiness.

dazza61

 

[John Big Booty]

You could use a query as the row source for your combo box, and in the query exclude any Skills that have already been selected.

 

[dazza61]

Thank you for the reply John...

I understand the using the query for the combo, it's the second bit you mentioned that I'm struggling with i.e. about the excluding records that have already been selected...if I use a <> operator in a query it excludes ALL records before they've been selected so I don't see any output. I also know that I'm gonna hafta requery the combo every time I select/enter it to reflect any changes, etc...

I really should know how to do this but I'm returning to Access after being away a while and I've got rusty so I'm sorry to sound dumb.

Any chance of an example or maybe a link that I've missed on here?

Many thanks in advance...

dazza61

 

[pbaldy]

Don't know your table structure, but one way, generally speaking:

SELECT Skills FROM SkillTable
WHERE Skills NOT IN (SELECT Skills for this employee)

 

[John Big Booty]

Try This out, is it what you are wanting to do?

 

[dazza61]

Paul's snippet I got lost with at first but then I stumbled on new things and used it for something else LOL - Nice one and thank you

John - Nice example and bang on! - Tweaked it to suit my needs so that it had the same effect all within the same form - Many thanks

dazza61