Lookup only selected values

[Guest]

I have a lookup in one of my tables that refers to a cost code. Each year
the cost code description changes (ie. J for 2004 = salary, J for 2005 =
Utilities). Is there a way that I can modify my lookup so that it will only
let me see/select correct values for a given record? That is, if I have an
expense listed for 2004, I don't want to be able to select J = 2005 =
utilities. Is there any way to limit my options based upon the value for
another field in the same record? At the moment, I've Mickey-moused my way
around the issue, by making a combined field called "datecode" where I
concatinated code and date to give "2005_J," for example. Not nearly as
tidy, however. Thoughts?
Thanks.

 

[John Vinson]

I have a lookup in one of my tables that refers to a cost code. Each year
the cost code description changes (ie. J for 2004 = salary, J for 2005 =
Utilities). Is there a way that I can modify my lookup so that it will only
let me see/select correct values for a given record? That is, if I have an
expense listed for 2004, I don't want to be able to select J = 2005 =
utilities. Is there any way to limit my options based upon the value for
another field in the same record? At the moment, I've Mickey-moused my way
around the issue, by making a combined field called "datecode" where I
concatinated code and date to give "2005_J," for example. Not nearly as
tidy, however. Thoughts?
Thanks.

It sounds like you should have a table with three fields: CodeYear
(don't use Year as a fieldname, it's a reserved word), CostCode, and
Value, with records like

2004 J Salary
2005 J Utilities

Your combo box (don't use Lookup fields in table datasheets, period:
see http://www.mvps.org/access/lookupfields.htm for a critique) could
then be based on a Query selecting just the current (or desired) year.

John W. Vinson[MVP]

 

[Guest]

Thanks for the suggestion. Perhaps lookups should come with a warning; most
unfortunate to have them included at all if they are so prone to misuse or
corruption.
JKS

 

[John Vinson]

Thanks for the suggestion. Perhaps lookups should come with a warning; most
unfortunate to have them included at all if they are so prone to misuse or
corruption.

The MVP community and the Microsoft developers very definitely do not
see eye to eye on this issue. There was an... ummm... lively would be
a polite way to describe it... discussion at the last Summit.

John W. Vinson[MVP]