Filter look up values

  • Thread starter Thread starter Dazed and Confused
  • Start date Start date
D

Dazed and Confused

I'm creating a form with two look up fields. Both fields are based on values
from the same table. I would like the values in the second field to be
filtered based on my response to the first field. So, for example, the first
look up field has a list of eight program types (i.e. education programs,
social programs, Awareness programs, etc.) Each program type has has between
10-12 related programs. If I select "Education Program" from the first list,
I want the second field to display only the 11 10 education programs in the
database. If I seletced "Awareness Programs," I want the second list to
display the 9 awareness programs in the database. I've tried everything I
can think of to make this work, can someone help me figure this out?
 
Dazed and Confused said:
I'm creating a form with two look up fields. Both fields are based on
values
from the same table. I would like the values in the second field to be
filtered based on my response to the first field. So, for example, the
first
look up field has a list of eight program types (i.e. education programs,
social programs, Awareness programs, etc.) Each program type has has
between
10-12 related programs. If I select "Education Program" from the first
list,
I want the second field to display only the 11 10 education programs in
the
database. If I seletced "Awareness Programs," I want the second list to
display the 9 awareness programs in the database. I've tried everything I
can think of to make this work, can someone help me figure this out?
Click to expand...


There's an example at the following URL ...

http://www.mvps.org/access/forms/frm0028.htm
 
It is easy to do with Controls on Forms but if you are using Access' "Lookup
Fields" defined in the table, you cannot do what you want. Brendan has given
you an appropriate reference for doing this on a Form. Your first action
will be to set up the two lookup Tables corresponding to the Lookup Fields,
and use the id of each entry as the Field in your table.

The Lookup Field approach obscures what is really stored, violates
relational database design principles, and I have yet to determine why the
Access team thought it a worthwhile thing to include. It is only useful when
users are working directly with the Table, and even when it is your own
personal Database, working directly with the Table is not a good idea.

Larry Linson
Microsoft Office Access MVP
 
Back
Top