Table field lookup pickle

[Guest]

I have a table where i use a lookup for one of the values. The lookup uses
values from another table where the visible column is the description, and
the saved column is a number (not visible).
The problem I have is, it is possible to have duplicate numbers, but the
visible description is different, making the table to appear wrong, and
difficult to check.
the table is set up as follows:
FILEnO; cOUNTY; eNTITYnO

The eNTITY is looked up from a table as follows:
eNTITY; eNTITYnO;cOUNTY,description

There are multiple EntityNo for County, but the Entity is different. The
entityNo for one table is different than the entityNo from the other. That is
why I need to save the value, but see the description.

If I haven't confused the giant knowledge base out there, I would appreciate
any input.

 

[John Vinson]

I have a table where i use a lookup for one of the values.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
feature. You're MUCH better off using your table just as a data
repository; you can use a Form with combo boxes on it to store the ID
and display the description.

The lookup uses
values from another table where the visible column is the description, and
the saved column is a number (not visible).
The problem I have is, it is possible to have duplicate numbers, but the
visible description is different, making the table to appear wrong, and
difficult to check.

Very easy to check if you don't conceal the actual content of the
table of course!

the table is set up as follows:
FILEnO; cOUNTY; eNTITYnO

With that very strange casing? oh well... hard for me to read but it's
not my database!

The eNTITY is looked up from a table as follows:
eNTITY; eNTITYnO;cOUNTY,description

There are multiple EntityNo for County, but the Entity is different. The
entityNo for one table is different than the entityNo from the other. That is
why I need to save the value, but see the description.

In a Form - but not in a table lookup field - you can change the
RowSource of the combo box to a Query selecting only the entities
appropriate to that county. Just base the combo on a query with a
criterion

=Forms![FormName]![cboCounty]

and Requery the combo in the afterupdate event of cboCounty.

No way I know of to do this in a table (one of the many limitations of
Lookup fields in tables).

John W. Vinson[MVP]