populate list box..

[Sreevani Koneru]

Hi,
I have 2 list boxes on my form...
I need to populate the second list box based on the value selected on the first one...
i have some sample code..

Private Sub List4_AfterUpdate()
Dim strTable As String
    strTable = Me.Ins
    Me.List4.RowSourceType = "Table/Query"
    Me.List4.RowSource = "SELECT DISTINCT [Portfolio Management].[Delivery Dept] FROM [Portfolio Management] _ 
WHERE [Portfolio Management].[Delivery Institution])= '&strTable';"
    Me.List4.Requery
End Sub

..but i am not getting any data in my second list box...
I am new to access..please let me know where i m going wrong?or suggest some better ideas..??

Thanks
Vani

[Stewart Ross]

You need to split the WHERE part of the string in line 6 so that you are including the value of strTable in the where clause, which you are not doing at present:

"SELECT ... WHERE [Portfolio Management].[Delivery Institution] = '"& strTable & "';"

The three dots '...' just means include the other parts of your SELECT statement as before (the DISTINCT keyword followed by your field names).

-Stewart

[Sreevani Koneru]

Dear Stewart,

Thanks for the reply...but i tried using your syntax...
but i am still not getting my second list box populated when i select my first one..

I am writing this code in the after update event of the second list box..

Please let know where i am going wrong..
I tried the code in the after update event of first list box also..

nothing helped..

Thanks

[Stewart Ross]

We have an Insights article on cascading combo/listboxes which explains in much more detail than I can do here how to set up the code concerned.

Please be clear that in my post above I simply corrected an obvious error in your original code; without further information on how what your SQL statement actually contains when your code is run, and the context in which you are running it, I can't comment on what might be wrong with your current interpretation of cascading listboxes.

-Stewart

[Sreevani Koneru]

I have done exactly what was described in the article u sent...infact i tried this concept seeing tht article only..

I am also able to print the recordsource with the value i passed..

SELECT DISTINCT [Portfolio Management].[Delivery Dept] FROM [Portfolio Management]WHERE [Portfolio Management].[Delivery Institution])='HSC';

but still i m not getting the list box populated..


my code:


Private Sub Ins_AfterUpdate()
Dim strTable As String
strTable = Me.Ins
Me.Dept.RowSourceType = "Table/Query"
Me.Dept.RowSource = "SELECT DISTINCT [Portfolio Management].[Delivery Dept] FROM [Portfolio Management]WHERE [Portfolio Management].[Delivery Institution])='" & strTable & "';"
Me.Dept.Requery
Debug.Print Me.Dept.RowSource

End Sub

[Sreevani Koneru]

Dear Stewart,

I got the issue resolved finally...I need to now see how to pass multiple values selected in list box to the query to get the results...Please let me know if u have idea on this..

Thanks
vani