depending listboxes

[Jean-Paul]

Hi,
I have a form with 2 listboxes.
the second one should be based upon the first one, so, if I go to the
next value in the first listbox, the second one should change too.

I wrote this code:

Private Sub Keuzelijst0_Click()
Dim sql As String
sql = "SELECT Sub_doel.Hoofddoel, Sub_doel.Sub_doel,
Sub_doel.Omschrijving FROM Sub_doel WHERE Sub_doel.Hoofddoel= " &
Forms!Doelstellingen!Keuzelijst0 & " ORDER BY Sub_doel.Sub_doel;"

Me!Keuzelijst2.RowSource = sql
Me!Keuzelijst2.Requery
End Sub

But, when nothing changes when I change Keuzelijst0

(finally, more listboxes chould change when the first one is changed)

What am I doing wrong?

Thanks

 

[NetworkTrade]

troubleshoot: run the sql as a query....and after you select in list1 ...run
this query and see if its results are correct

 

[Douglas J. Steele]

Try putting the code in the AfterUpdate event, not the Click event.

 

[Jean-Paul]

The sql is coorect and shows the correct records

 

[Jean-Paul]

I did what you suggested:


Private Sub Keuzelijst0_AfterUpdate()
Dim sql As String
sql = "SELECT Sub_doel.Hoofddoel, Sub_doel.Sub_doel,
Sub_doel.Omschrijving FROM Sub_doel WHERE Sub_doel.Hoofddoel= " &
Forms!Doelstellingen!Keuzelijst0 & " ORDER BY Sub_doel.Sub_doel;"
Me!Keuzelijst2.RowSource = sql
Me!Keuzelijst2.Requery
End Sub

Same result... no records displayed in the second listbox

 

[Douglas J. Steele]

When you say that the SQL is correct and shows the correct records, how did
you determine that?

Your code is

Dim sql As String
sql = "SELECT Sub_doel.Hoofddoel, Sub_doel.Sub_doel,
Sub_doel.Omschrijving FROM Sub_doel WHERE Sub_doel.Hoofddoel= " &
Forms!Doelstellingen!Keuzelijst0 & " ORDER BY Sub_doel.Sub_doel;"
Me!Keuzelijst2.RowSource = sql
Me!Keuzelijst2.Requery

What happens if you change that to

Dim sql As String

sql = "SELECT Sub_doel.Hoofddoel, Sub_doel.Sub_doel,
Sub_doel.Omschrijving FROM Sub_doel WHERE Sub_doel.Hoofddoel= " &
Forms!Doelstellingen!Keuzelijst0 & " ORDER BY Sub_doel.Sub_doel;"

Debug.Print sql

Me!Keuzelijst2.RowSource = sql
Me!Keuzelijst2.Requery


After the code runs, go to the Immediate Window (Ctrl-G) and check the SQL
that was generated. Does it run correctly?

 

[Jean-Paul]

perfect...
I copy paste it as a query... correct records are displayed
In the second listbox.... nothing
JP

 

[Jean-Paul]

Guys... problem solved...

I accidently deleted the type of recordsource...

Thanks for your kind help
JP