if you want to base the list of choices on the value of another field in the
record, then a validation rule won't work. you need to do it the way i said,
in a form. it's an easy enough setup, but not something i can talk you thru
step-by-step here. if you want to send me a copy of your database, sans
proprietary data but with a few dummy records, then do the following:
1) compact the copy and zip to under 1 MB.
2) refer to the newsgroups in the email Subject line and send to
ttaccKILLALLSPAMMess1 at DEADyahoo dot com, removing all the
capital letters.
i'll do a quick setup and email it back so you can see how it's done.

hth


"WaltS [MVP WMC]" wrote in message
...
Tina -

That ("seems i read somewhere that you can't reference one table field in
another table field") seemed to be (one of) the problem(s). So...

Making me think as you are smile...

I presume instead of lookup fields, you would use validation rules (LIKE
"red OR LIKE "blue" OR...) when dealing with "a few" items. Which begs the
question - is there a max number of characters to the size of a set of
validation rules.

If there is (and that limit would be), then how does one resolve the
following...

You have a large number of organizations that are identified by an entity
and a local name (e.g., Veterans of Foreign Wars - National, or American
Legion - Howard Van Wagner Post 962). You want to ensure uniformity of
input, without the use of lookup fields, but you would like Access to
help...

Thanks again...

--

--- REgards walts ---
- - _ ---
- - |~| - -
--- ^ ---
- - ---
- - 52-Ken (WMC-MVP) Sun-57 - -



"tina" wrote in message
...
seems i read somewhere that you can't reference one table field in
another
table field - but since i don't use lookup fields in tables, i've never
tried it.
in a form, you can filter a combo box control's "droplist" based on the
contents of another control, by setting a full reference to the other
control as a criteria in the RowSource query or SQL statement.


"WaltS [MVP WMC]" wrote in message
...
Tina - (and others here)

That said ("The Evils of Lookup Fields in Tables"), is there a way to
do
what Susan has asked?

I, too, would like to have the "lookup" displayed results limited
based
on
the contents of another field in the current record, and where the
lookup
source is another table, that in a field which is NOT the
to-be-displayed
field, has the same contents as that of the other field in the current
record.

Thanks in advance...

--

--- REgards walts ---
- - _ ---
- - |~| - -
--- ^ ---
- - ---
- - 52-Ken (WMC-MVP) Sun-57 - -



"tina" wrote in message
...
hi Susan. suggest you read the article at the following link before
putting
any lookup fields in any of your tables:
http://www.mvps.org/access/lookupfields.htm

hth


"Susan A" wrote in message
...
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.