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

  • Thread starter Thread starter James H. Power
  • Start date Start date
J

James H. Power

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.
 
Hi Jim,

You can't perform such in Microsoft Access. In SQL Server you can
accomplish such using Triggers, Constraints, and Stored Procedures.

In Access your workaround (as you stated you can create) is to use Forms.
You can set your Form to appear in datasheet view (same look as a Table).

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "James H. Power" <[email protected]>
| Sender: "James H. Power" <[email protected]>
| Subject: Make a field lookup dependent on the value in another field of a
record?
| Date: Mon, 8 Mar 2004 12:43:23 -0800
| Lines: 46
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQFTf+Pcy1xSTwYSaa1I/SJgkHJqw==
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:76829
| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| 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.
|
 
Eric: That's what I was afraid of, but your idea of a
form appearing as datasheet view is a good one, and is
what I'll do. Thanks for the very quick reply!

Jim P.
-----Original Message-----
Hi Jim,

You can't perform such in Microsoft Access. In SQL Server you can
accomplish such using Triggers, Constraints, and Stored Procedures.

In Access your workaround (as you stated you can create) is to use Forms.
You can set your Form to appear in datasheet view (same look as a Table).

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03 -026.asp> and/or
to visit Windows Update at
Click to expand...
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights
Click to expand...
 
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

o

"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
Susa

----- 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 Field
a
b
c
c
d
d

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 Field
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
 
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 said:
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
Click to expand...
limit them based on the previous entry if possible, so that people entering
data don't need as much training.
 
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 ---
- - _ ---
- - |~| - -
--- ^ ---
- - (e-mail address removed) ---
- - 52-Ken (WMC-MVP) Sun-57 - -
 
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.
 
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 ---
- - _ ---
- - |~| - -
--- ^ ---
- - (e-mail address removed) ---
- - 52-Ken (WMC-MVP) Sun-57 - -
 
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
 
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 ---
- - _ ---
- - |~| - -
--- ^ ---
- - (e-mail address removed) ---
- - 52-Ken (WMC-MVP) Sun-57 - -


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
 
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
 
Tina -

Thanks - now to think on it <smile>

--

--- REgards walts ---
- - _ ---
- - |~| - -
--- ^ ---
- - (e-mail address removed) ---
- - 52-Ken (WMC-MVP) Sun-57 - -
 
Back
Top