cascading List Boxes in forms

Finance · Jul 16, 2018

Hi,

is there a way to create cascading list boxes in a form?
both the list boxes draw data from the same table and they are extended list boxes for multiple selection.

List Box 1: Years
List Box 2: nature of Fees.

I want only those fees to be visible that are connected to the selected years. the other fees should not be visible.

Thanks!

pbaldy · Jul 16, 2018

Same as combos:

http://www.baldyweb.com/CascadingCombo.htm

pbaldy · Jul 16, 2018

Oh, for multiselection you'll need to loop the first one. Use the strWhere in the SQL
method above:

http://www.baldyweb.com/multiselect.htm

Finance · Jul 16, 2018

This is my code

Code:

Private Sub ListYears_AfterUpdate()
    Dim frm As Form_SelectionCriteriaForm, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
        
        [B][U]Set ctl = frm.ListYears[/U][/B]
        Set ctl2 = frm.lstNatureOfFees
        
        strSQL = "Select * from Nature_of_Fees where [Years_Word]="
        For Each varItem In ctl.ItemsSelected
            strSQL = strSQL & ctl.ItemData(varItem) & "or[Years_Word]="
        Next varItem
        
        strSQL = Left$(strSQL, Len(strSQL) - 12)
        
        Debug.Print strSQL
        ctl2.Requery
        
               
        
End Sub

theres an error in the highlighted part. Any suggestions?
Thanks!

Finance · Jul 16, 2018

I will try your suggestion. Thanks!!

pbaldy · Jul 16, 2018

That bit in the dim line looks wonky; I don't think you ever set the form variable. Try simply

Set ctl = Me.ListYears

MajP · Jul 16, 2018

Code:

Dim frm As Form_SelectionCriteriaForm

That does not open or instantiate the object.

either

Code:

Set frm = forms("SelectionCriteriaForm")

or

Code:

Dim frm as NEW Form_SelectionCriteriaForm
frm.visible = true

If already open do not use the latter.

Finance · Jul 17, 2018

The nature of fees list box isnt sorting but the code isnt throwing an error.
COuld you look at the form on my database and maybe point out wheres the mistake. That would be a great help.
Thanks

JHB · Jul 17, 2018

Replace what you have with the below:

Code:

Private Sub ListYears_AfterUpdate()
  Dim varItem As Variant
  Dim strSQL As String
        
  If Me.ListYears.ItemsSelected.Count Then
    strSQL = "Select ID_Number, Nature_of_Fees from CostSheet where [Years_Word] in ('"
    For Each varItem In Me.ListYears.ItemsSelected
      strSQL = strSQL & Me.ListYears.ItemData(varItem) & "','"
    Next varItem
    strSQL = Left(strSQL, Len(strSQL) - 2) & ") ORDER BY ID_Number"
  Else
    strSQL = "Select ID_Number, Nature_of_Fees from CostSheet ORDER BY ID_Number"
  End If
  Me.lstNatureOfFees.RowSource = strSQL
End Sub

Finance · Jul 17, 2018

Thank you sooo much!!!!!!
It worked like a dream. Thanks!

JHB · Jul 18, 2018

You're welcome.

cascading List Boxes in forms

Finance

Registered User.

pbaldy

Wino Moderator

pbaldy

Wino Moderator

Finance

Registered User.

Finance

Registered User.

pbaldy

Wino Moderator

MajP

You've got your good things, and you've got mine.

Finance

Registered User.

Attachments

JHB

Have been here a while

Finance

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread