Selective Field

[Novice1]

I have a table (CSR) that lists all our customer service reps to include the name, position, (Y/N), etc.

I can add new reps but don't delete them when they leave because I need to keep CS info in another form; instead, I mark the rep as Inactive (Y) when they depart.

I have another form (Training Event) that's linked to the CSR table. In the CS Rep field I have a drop down field so that only the last names of the ACTIVE reps appear. All is well and good but previous records don't show inactive CS rep.

What I want is a drop down field that only shows active CS reps when I create new training events but maintain the names of the active or inactive CS reps in older records.

Any help would be appreciated.

 

[MarkK]

Well, you can't have it both ways, so what I sometimes do in a case like that is sort the active data to the top of the combo. Also, leave the inactive data in there too, but maybe prefix it with 'x - ', so imagine a combo.rowsource like...

SELECT RepID, IIF(Inactive, "x - ", "") & Lastname As RepName
FROM tRep
ORDER BY IIF(Inactive, "x - ", "") & Lastname

This way all your data is present in the combo, but the active and inactive reps are easily distinguished.

 

[Novice1]

Thanks ... that's a clever solution