Make a field lookup dependent on the value in another field of a record?

I am trying to do the same thing, but am not sure what you mean by

"set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply)

or

"your idea of a form appearing as datasheet view is a good one, and is
what I'll do" (from your reply to Eric).

In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training.

Thanks for any suggestions,
Susan

----- James H. Power wrote: -----

Hello: In table design you can use the lookup wizard to
provide a list of possible values for a field that's
obtained from another table. Is it possible to further
limit the values in the lookup list, based on the value of
another field in the current record? For example, suppose
I have a lookup TableA with fields and values as follow:

TableA:
FieldA FieldB
a 1
b 2
c 3
c 4
d 5
d 6

Then suppose I'm developing the design of TableB with
fields and values as follow, where FieldD is where I want
to have the lookup values appear:

TableB:
FieldC FieldD
c ?
d ?

FieldC has values that correspond to those in FieldA.
What I'd like is that when the value in FieldC is "c", the
lookup list returns only the corresponding values from
FiledB of TableA where FieldA has the value "c", so that
lookup list for FieldD then only shows possible values
of "3" and "4". When the value in FieldC is "d", then the
lookup list for FieldD only shows possible values of "5"
and "6", and so forth. An approximation of the SQL
statement that would achieve this would be

SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE
(TableA.FieldA = "c")

Except that I would like the quoted "c" in the above SQL
statement to be the value of FieldC in the current
record. I figure I can do this with a control on a form,
but I'd rather put it into the table's design.

Can anybody help? (and I hope my question made sense).

Jim P.