Problem creating Multi level list boxes - please help!

[AccessConfused1]

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?

 

[dhookom]

The control references in the where clauses must include the reference to the form like:
Forms!frmYourForm!lboYourList

IMO, you also need to change your list box names to be more descriptive and professional.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

replace this:
WHERE ((([Project Names].[Project Type ID])=[list13].[value]));
with this:
WHERE [Project Names].[Project Type ID]=[Forms]![name of your mainform]![list13]

and this:
WHERE (((Custodian_T.Custodian_ID)=[Custodian].[value]));
with this:
WHERE Custodian_T.Custodian_ID=[Forms]![name of your mainform]![Custodian]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886