I have a table named Notes with 4 fields:
ID (Autonumber),
Project (Lookup to ProjectTable.Name),
Task (Lookup to TaskTable.Name),
Note (Memo field)

The Task Table has a lookup field to the ProjectTable.Name field. What I would like to do is filter the available selections of the combobox for the [Note].[Task] field depending on what was selected in the [Note].[Project] field. I only want the user to able to select the Tasks that are related to the Project. In other words, how do you filter the values of Lookup field base off of a value of different field in the same table as the Lookkup field?

I've tried adding a Where clause to the RowSource display control for the Note.Task field but that did not work.

Thanks in advance for any help.

Welcome to Utter Access Forums!

What you are looking for is called Cascading Combo Boxes. A search through the forums will yield plenty of threads... you can also check out Utter Angel.


When you say "Project (Lookup to ProjectTable.Name), " -- do you mean a FK relationship or do you mean you are using a lookup field in the table? If the latter, you should avoid it -- here's why: The Evils of Lookup Fields in Tables

Thanks Doug. I will look into that. Regarding your second statement, I am using a Lookup field in the table. If lookup fields are not recommended what would be another solution?

Welcome to UtterAccess!!

Your table structure does not look right. If the "Task Table" is storing the "Project Name" then there is no need to store that in the Notes table as well. Also, you should not be storing the names in the related tables, but the primary keys. For example...

tblProjects
ProjectID (PK, autonumber)
ProjectName (Text)
etc...

tblProject_Tasks
TaskID (PK, autonumber)
ProjectID (FK, Long Int)
etc...

tblTask_Notes
NoteID (PK, autonumber)
TaskID (FK, Long Int)
TaskNote (Memo)

And you should also not be using look up fields at the table level. [*] The Evils of Lookup Fields in Tables

You are welcome.

Use 'regular" fields... in the forms, you will use a combo box to display the description values for the field while storing the FK values in the table...

See Kristen post for structure suggestion...

Sorry Doug...I did my reply and had to leave my desk for a while and that is why I posted WAY after the fact!

No worries Kristen... happens to me all the time :-)

Besides, you caught the structure issue that I missed.

Thanks for the responses. Very helpful. Kristen, my table structure is as described above except I do need the Project Name in the Notes Table being as that I want to have the option of relating a record in the Notes table to either a Project, a Task or both. I'm just not sure how to impose a foreign key constraint in Access so that the user can only select a Task that is related to the project.

>> I want to have the option of relating a record in the Notes table to either a Project, a Task or both

I note related to a task is related also the the task's project... so task and both is covered by Kristen's structure.

If you need project specific notes that are not related to a task, you may want to have a separate table for that... or modify the table structure to allow for Note Type selection (task or project)...


>> I'm just not sure how to impose a foreign key constraint in Access so that the user can only select a Task that is related to the project.

That's where the cascading combo boxes come to play