Table Lookup Field - options relative to record

[Swelfel]

My question concerns how to write the row source for a lookup field in a
table such that the options listed in the lookup dropdown menu are dependent
upon the record – the row. So I want the options available in the dropdown to
be different for each record in the table. If it is not possible to do this
in a table, I would at least like to be able to do this for a combo box a
form.

The database I'm building (on Access 2003) is a database of properties we've
inspected, and a bunch of relevant features of those properties. The tables
and their pertinent field relevant to my question are:
Properties table
PropertyID (PK)
People Table
PersonID (PK)
Ownership Table (which is a Junction table creating a many to many
relationship between Properties and People)
OwernshipID (PK)
Property ID (FK)
PersonID (FK)
Hearing table (linked to Property table by PropertyID which is included as a
foreign key in this table)
HearingID (PK)
PropertyID (FK)
Hearing Attendees Table (Junction table creating a many to many relationship
between Hearings and People)
AttendeeID (PK)
Hearing ID (FK)
PersonID (FK)

I would like the PersonID field in the Hearing Attendees table to be lookup
field that displays a list of People relevant for the Property which is the
subject of that Hearing. I know how to write a SQL query for a lookup field
that pulls values options other tables, but I don’t know how to get the query
to look at the specific record. Here is a description of the “thinking
process†that I would like the lookup field to do:

Suppose I’m in the Hearing Attendees table looking at a record where the
hearing ID is “2â€. When I click on the dropdown for PersonID, I would it to
look in the Hearings Table to find that Hearing 2 is linked with Property
236-12, then look in the Ownership Table to find all the owners for Property
236-12 and return that list of owners to the dropdown as the options from
which I can select.

Is this possible on a table, or only on a form? If it’s only possible on a
form, I would greatly appreciate an explanation for how to implement it on a
form.

Thank you so much!

 

[Jeff Boyce]

Stop now! The use of a lookup datatype field in an Access table will cause
you and whoever maintains the application after you considerable
consternation. That's because it shows one thing (the looked up value) in
the table, even though it is storing the primary key of that looked up
value.

Access tables store data (Access is not a spreadsheet); Access forms display
data. Use a combobox on a form to do your lookup.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.