Problem creating Multi level list boxes - pleas help!

[AccessConfused]

Hi, I have created a form that has 3 list boxes.

The first list box contains the project name. Based on whatever is selected in the Projects list box, the 2nd list box is generated with Custodian names associated with that project. I am having problems generating the 3rd list box. This holds the data sources associated with whatever is selected in the 2nd list box (Custodians).

List13 = Project listbox
Custodian = Custodian listbox
List15 = Data source listbox


The first 2 are working but I am not able to generate anything for the 3rd list box.


In my VB Code, I have:

Private Sub List13_AfterUpdate()
Custodian.Requery
End Sub

Private Sub Custodian_AfterUpdate()
List15.Requery
End Sub

When I switch to form view, I get the following error:

"This expression has been typed incorrectly, or is too complex to evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."



My SQL query for the second listbox (Custodians) :

SELECT DISTINCT Custodian_T.Name_First_NV, Custodian_T.Name_Last_NV, Project_T.Custodian, [Project Names].[Project Type ID]
FROM Custodian_T INNER JOIN ([Project Names] INNER JOIN Project_T ON [Project Names].[Project Type ID] = Project_T.Project_Type) ON Custodian_T.Custodian_ID = Project_T.Custodian
WHERE ((([Project Names].[Project Type ID])=[list13].[value]));


My SQL query for the third listbox (Data Source):

SELECT Data_Source_T.Name_NV, Project_T.Data_Source, Project_T.Custodian, Custodian_T.Custodian_ID
FROM Data_Source_T INNER JOIN (Custodian_T INNER JOIN Project_T ON Custodian_T.Custodian_ID = Project_T.Custodian) ON Data_Source_T.Name_NV = Project_T.Data_Source
WHERE (((Custodian_T.Custodian_ID)=[Custodian].[value]));


What am I doing wrong here? How can I get my third list box to generate anything?

[pbaldy]

In a query you need the full form reference:

WHERE ((([Project Names].[Project Type ID])=Forms!FormName.
[list13]));

[AccessConfused]

I tried that, it doesn't work either. My first 2 list boxes are working properly, there is something that I am doing incorrectly for the 3rd list box.

[pbaldy]

If you paste the SQL into a query does it run?

[AccessConfused]

Yup when I paste it into a separate query (minus the WHERE clause) it runs.

The "WHERE" is the part that generates something based on the previous lists selection.

So why isn't this working? This error message keeps popping up:

"This expression has been typed incorrectly, or is too complex to evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

[pbaldy]

So it doesn't run if you paste the whole thing into a query and run it? What is the actual SQL now?

[AccessConfused]

Yes I meant it works when I copy and paste that into a separate query. This is not letting me link link tables..