Dec 17 2009, 12:09 PM
I have a table named Notes with 4 fields:
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.
Dec 17 2009, 12:37 PM
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
Dec 17 2009, 01:05 PM
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?
Dec 17 2009, 01:18 PM
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...
ProjectID (PK, autonumber)
TaskID (PK, autonumber)
ProjectID (FK, Long Int)
NoteID (PK, autonumber)
TaskID (FK, Long Int)
And you should also not be using look up fields at the table level. [*] The Evils of Lookup Fields in Tables
Dec 17 2009, 01:23 PM
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...
Dec 17 2009, 01:43 PM
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!
Dec 17 2009, 01:45 PM
No worries Kristen... happens to me all the time :-)
Besides, you caught the structure issue that I missed.
Dec 17 2009, 02:33 PM
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.
Dec 17 2009, 02:40 PM
>> 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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here