Oh Board Masters...need help with Combo/List Box

[foxhound]

ray: Oh Board Masters…I need help! I have 6 list boxes on a form. I need to have the first one filtered as such… Me.Super.RowSource = ("Select DISTINCT [SUPV] from Employees") Then, I need to filter each subsequent box based on the selected item in the previous box. Can someone help?

Here is the code that I am working on:

Private Sub Form_Load()

Me.Super.RowSource = ("Select DISTINCT [SUPV] from Employees")

End Sub

Private Sub Super_AfterUpdate()

'Select a supervisor from 1st supervisor list to use as filter
'for 2nd list/combo box
Me.Super1.RowSource = "select [name] from employees where [empno] = Me.Super.ItemData(0))"
Me.Super1 = Me.Super1.ItemData(0)

'Select a supervisor from 2nd supervisor list to use as filter
'for 3rd list/combo box
Me.Super2.RowSource = "select [name] from employees where [empno] = Me.Super1.ItemData(0))"
Me.Super2 = Me.Super2.ItemData(0)

'Select a supervisor from 3rd supervisor list to use as filter
'for 4th list/combo box
Me.Super3.RowSource = "select [name] from employees where [empno] = Me.Super2.ItemData(0))"
Me.Super3 = Me.Super3.ItemData(0)

'Select a supervisor from 4th supervisor list to use as filter
'for 5th list/combo box
Me.Super4.RowSource = "select [name] from employees where [empno] = Me.Super3.ItemData(0))"
Me.Super4 = Me.Super4.ItemData(0)

'Select a supervisor from 5th supervisor list to use as filter
'for 6th list/combo box
Me.Super5.RowSource = "select [name] from employees where [empno] = Me.Super4.ItemData(0))"
Me.Super5 = Me.Super5.ItemData(0)

End Sub

 

[dk]

Hi,

You just need to include the Me.Super.ItemData bit outside of the " of your string. Something like this should work:-

Me.Super1.RowSource = "select [name] from employees where [empno] = " & Me.Super.ItemData(0) & ")"

 

[foxhound]

Thanks for the reply. Okay, I have tried that but when I select on the first combo box is does nothing, i.e. it does filter the records in super1 based upon the record chosen in super.

 

[dk]

Hi,

I just noticed that you're using ItemData(0) - that will always return the same value - the first value in the list regardless of what the user has actually selected. I think you need the Text property.

I just did a quick test and this works fine:-

Private Sub Combo0_AfterUpdate()

Me.Combo2.RowSource = "SELECT * FROM Employees where empno=" & Me.Combo0.Text

End Sub

 

[foxhound]

Hey Dk,

I made the change and now I get an error that it doesn't like the ".text" on the end. It doesn't recognize it. Error is: "Method or Data Member not found." Any further suggestions that you can offer?

 

[dk]

Sorry mate, I didn't read your question properly. I thought you were using comboboxes, not listboxes. Instead of Text, try Value e.g.

Me.List2.RowSource = "SELECT * FROM employees WHERE Empno=" & Me.List0.Value