Lookup field in a table

[Jey]

I am creating a table that has a lookup field based on
another table and have chosen 3 columns. When I open the
table in Datasheet View, I can only see the first column.
Is there a way to view all 3 columns without having to use
the drop-down box? I can write an expression that shows
all three, but I want to write an update query based on
the 3 columns, so I need these to remain seperate, but
together (if that makes sense).

 

[Ted]

Hi,

I'm not sure I completely understand what you are doing,
but I will offer the following in case it will help.

Generally, when you have a lookup table you would have a
key field that uniquely identifies each record so that
other tables that reference records from the table would
only store the record's Key value (ID). Then, you would
use that column as the bound column for the combo box.
If you wanted to display something other than the ID, you
would increase the column count and set the width of the
first column to 0. Then the second column would be
displayed. If you wanted to see more than one column
from the source table, you could create an expression in
the second column to concatenate whatever columns that
you want to display, but you would still only be storing
the record ID.

Using this approach, there would not be a problem with
running queries that would use the individual column
values, because you would just insert both tables into
your query source and link the ID in the main table to
the ID in the lookup table.

If, for some reason, you cannot use a unique ID in your
lookup table, and you need to insert all three columns
into one in your table, but still be able to work with
them separately, I guess you could concatenate all three
in column 0 with some sort of delimiters between the
values so that they could be parsed again later if needed
(by using Instr(), left(), mid() and right() functions).
This would be workable, but very undesireable in most
circumstances.

Hope that helps. If I misunderstood your question please
post back.

-Ted

 

[Tim Ferguson]

When I open the
table in Datasheet View,

Just don't. Use a form, with a subform if that helps.

Tim F