Solved Multiple listboxes on main form (3 Viewers)

[soupy8728]

Good morning all,

I have a main form with 3 list boxes on it. The first list box lstBox1 filters the second list box lstBox2 with no problem. I make a selection in lstBox1 and it filters lstBox2 as intended. The problem I'm having is the third list box lstBox3. lstBox3 will only update if I click on the filtered item in lstBox2. I was hoping that the After Update event triggered from lstBox1 would filter lstBox2 AND lstBox3 but it doesn't seem to work that way. Can this be done where I select an item in lstBox1 and lstBox2 AND lstBox3 update together? Thank you!!

Soup

 

[Gasman]

Short answer is Yes.
For your situation all the code should be in the afterupdate of list1.
For the most part, usually list1 filters list1 and list2 filters list3 and so on, but for your situation everything should be in list1.

Show your code (between code tags please) The </> icon.

 

[soupy8728]

Short answer is Yes.
For your situation all the code should be in the afterupdate of list1.
For the most part, usually list1 filters list1 and list2 filters list3 and so on, but for your situation everything should be in list1.

Show your code (between code tags please) The </> icon.

Here is what I have for After Update in lstBox1 (as suggested):

me.lstBox2.requery
me.lstBox3.requery

this still doesn't do it all with a single selection of lstBox1. I still am required to select the filtered item in lstBox2 in order for lstBox3 to update.

Hope this is all making sense. Thanks!

 

[MajP]

I find it really hard to do this using parameters in the queries, and always find it easier to do this with sql strings in the code. Especially when you have more than 2 lists/combos.

This way I can make a single procedure to filter and call that procedure from multiple events as needed.

Private Sub lstOne_AfterUpdate()
 
  Me.lstTwo = Null
  Me.lstThree = Null
  FilterLists
End Sub

Private Sub lstTwo_AfterUpdate()
  Me.lstThree = Null
  FilterLists
End Sub

'******************************************************************************************************************************************************************
'------------------------------------------------------------------Divider ---------------------------------------------------------------------------------------
'******************************************************************************************************************************************************************
Public Sub FilterLists()
  If Not IsNull(Me.lstOne) Then
    Me.lstTwo.RowSource = "SELECT ProductID, ProductName FROM tblProducts where CategoryID = " & Me.lstOne
  Else
   Me.lstTwo.RowSource = "SELECT ProductID, ProductName FROM tblProducts"
  End If
 
  If Not IsNull(Me.lstTwo) Then
    Me.lstThree.RowSource = "SELECT [tblProducts].[ProductID], [tblProducts].[ProductName] FROM tblProducts where ProductID = " & Me.lstTwo
  Else
    Me.lstThree.RowSource = "SELECT [tblProducts].[ProductID], [tblProducts].[ProductName] FROM tblProducts"
  End If
End Sub

 

[arnelgp]

you can "force" Listbox3 to filter (based on Listbox2), by adding additional code
ot Listbox1 AfterUpdate event.

Private Sub Listbox1_BeforeUpdate(Cancel As Integer)
Listbox2.Requery
If Listbox2.ListCount <> 0 Then
    Listbox2 = Listbox2.ItemData(0)
End If
Listbox3.Requery
End Sub

 

[Gasman]

Here is what I have for After Update in lstBox1 (as suggested):

me.lstBox2.requery
me.lstBox3.requery

this still doesn't do it all with a single selection of lstBox1. I still am required to select the filtered item in lstBox2 in order for lstBox3 to update.

Hope this is all making sense. Thanks!

You need to show the source for the listboxes, again within code tags please.

 

[soupy8728]

you can "force" Listbox3 to filter (based on Listbox2), by adding additional code
ot Listbox1 AfterUpdate event.

Private Sub Listbox1_BeforeUpdate(Cancel As Integer)
Listbox2.Requery
If Listbox2.ListCount <> 0 Then
    Listbox2 = Listbox2.ItemData(0)
End If
Listbox3.Requery
End Sub

This worked perfectly! Thank you so much!!