A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2004, 09:11 PM
Susan A
external usenet poster
 
Posts: n/a
Default 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.

  #2  
Old May 10th, 2004, 11:00 PM
tina
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

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.



  #3  
Old May 21st, 2004, 02:59 PM
WaltS [MVP WMC]
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

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.





  #4  
Old May 21st, 2004, 05:45 PM
tina
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

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.







  #5  
Old May 21st, 2004, 06:49 PM
WaltS [MVP WMC]
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

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.









  #6  
Old May 21st, 2004, 09:35 PM
tina
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

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.











  #7  
Old May 22nd, 2004, 01:54 PM
WaltS [MVP WMC]
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

Tina -

Thanks again for your help - as I am trying to "learn" a tad about Access,
I'd rather fumble my way around a bit - I remember better that way.

And, if you don't mind spending some more of your time...

First a fact as I have come to understand it...

A lookup field in a table, based on another table, while it displays the
looked-up value, actually saves in the table an id from the other table.

From what you said, am I correct to infer...

A lookup field in a form, based on a table, used to create an entry in a
different table, actually saves the looked-up value in that table.

If I am correct, then I have learned a "subtle" fact, and, better understand
"The Evils of Lookup Fields in Tables."

If I am wrong... well, er, some additional insight if you would.

--

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


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











  #8  
Old May 22nd, 2004, 08:15 PM
tina
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

i usually stick with a thread, once i post to it, until it has been dormant
for a couple weeks. i'll always answer specific questions, or at least
declare my lack of knowledge g; i just haven't the time to try to give
detailed instructions for a whole process to achieve a particular solution.
and of course, anyone else in the newsgroups can post a response to any
question, too - no thread is exclusive. having said all that...
i think you're correct in your basic grasp of how to use a lookup table via
a combo box (or list box, for that matter) in a form. but just to clarify:
your form is based on tblA. you want to save an ID from the lookup table
into a field in tblA. so in the form, you create a combo box, bound to the
field in tblA, with the combo's RowSource based on the lookup table.

hth


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

Thanks again for your help - as I am trying to "learn" a tad about Access,
I'd rather fumble my way around a bit - I remember better that way.

And, if you don't mind spending some more of your time...

First a fact as I have come to understand it...

A lookup field in a table, based on another table, while it displays the
looked-up value, actually saves in the table an id from the other table.

From what you said, am I correct to infer...

A lookup field in a form, based on a table, used to create an entry in a
different table, actually saves the looked-up value in that table.

If I am correct, then I have learned a "subtle" fact, and, better

understand
"The Evils of Lookup Fields in Tables."

If I am wrong... well, er, some additional insight if you would.

--

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


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













  #9  
Old May 22nd, 2004, 09:10 PM
WaltS [MVP WMC]
external usenet poster
 
Posts: n/a
Default Make a field lookup dependent on the value in another field of a record?

Tina -

Thanks - now to think on it smile

--

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.