Question How to multiselect for cascading listboxes?

[shocktea]

Hi,

I am making a form at work for a coworker with cascading combo/list boxes where she can select a Customer, then Platform Description, then Period, then Year. I used VBA code for this and it's working perfectly - for single selection.

I want to update this so she can select multiple Platform Descriptions and/or Periods and it will give all the applicable info.

Can someone show me if there is a certain code I can use or a way to set up a query, etc. so that multi-select will work for me? Unfortunately, just changing the formatting to multiselect makes the listbox just blank white, so I'm assuming it's going to be more complicated. I'm new at Access, so hang in there with me!

Thank you!

(Here's the code I currently have for the cascading combo/list boxes, if you need it.
SD0039DA_T is the table name where my listboxes are pulling info from.)

Option Compare Database
 
Private Sub Form_Load()
On Error Resume Next
 
CustomerCB.SetFocus
PlatformDescriptionL.Enabled = False
PeriodL.Enabled = False
YearCB.Enabled = False
End Sub
 
 
Private Sub CustomerCB_AfterUpdate()
  On Error Resume Next
 
  PlatformDescriptionL.Enabled = True
  PlatformDescriptionL.RowSource = "Select distinct PlatformDescription " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' " & _
           "ORDER BY PlatformDescription"
  PlatformDescriptionL.SetFocus
  PeriodL.Enabled = False
  PeriodL.RowSource = "Select distinct Period " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' " & _
           "ORDER BY Period"
  PeriodL.SetFocus
  YearCB.Enabled = False
    YearCB.RowSource = "Select distinct BillingYear " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' AND " & _
           "Period = " & PeriodL & " " & _
           "ORDER BY BillingYear"
  YearCB.SetFocus
End Sub
 
Private Sub PlatformDescriptionL_AfterUpdate()
  On Error Resume Next
  PeriodL.Enabled = True
  PeriodL.RowSource = "Select distinct Period " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' " & _
           "ORDER BY Period"
  PeriodL.SetFocus
 
    YearCB.Enabled = False
    YearCB.RowSource = "Select distinct BillingYear " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' AND " & _
           "Period = " & PeriodL & " " & _
           "ORDER BY BillingYear"
  YearCB.SetFocus
 
End Sub
 
 
  Private Sub PeriodL_AfterUpdate()
  On Error Resume Next
 
  YearCB.Enabled = True
    YearCB.RowSource = "Select distinct BillingYear " & _
           "FROM SD0039DA_T " & _
           "WHERE CustomerName = '" & CustomerCB & "' AND " & _
           "PlatformDescription = '" & PlatformDescriptionL & "' AND " & _
           "Period = " & PeriodL & " " & _
           "ORDER BY BillingYear"
  YearCB.SetFocus
 
End Sub
 
 
Private Sub RunReportButton_Click()
End Sub

 

[pbaldy]

This is not exactly what you're trying to do, but it demonstrates building an "IN" clause from a multiselect listbox, which is what you can do.

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

 

[shocktea]

pbaldy - I looked up your link and from there found a code that might be more applicable to the multiple multiselect cascading listboxes, but I could be wrong. I entered in that code (and tried yours), but the Period turns blank once I begin to select the Platform Description.

Here is the code I found and used (changed the listbox names to the ones I am using). Can you or anyone help me and tell me what I am entering wrong, or a step-by-step on how to enter this so it will function? I'm newer to Access, so I'm trying to crash course through this multiselect part. If this code is completely inaccurate, also please let me know! It very well could be.

[FONT=Calibri][COLOR=black][FONT=Verdana]   Dim strWhere As String[/FONT][/COLOR]
[FONT=Calibri][COLOR=black]   Dim ctlList1 As Control[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Dim ctlList2 As Control[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Dim varItem1 As Variant[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Dim varItem2 As Variant[/COLOR][/FONT]
 
[FONT=Calibri][COLOR=black]   'make sure a selection has been made[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   If Me.PlatformDescriptionL.ItemsSelected.Count = 0 Then[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   MsgBox "Must make at least 1 selection"[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Exit Sub[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   End If[/COLOR][/FONT][/FONT]
[FONT=Calibri][COLOR=black][FONT=Verdana]   'add selected values to string[/FONT][/COLOR]
[FONT=Calibri][COLOR=black]   Set ctlList1 = Me.lstList1[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Set ctlList2 = Me.lstList2[/COLOR][/FONT]
 
[FONT=Calibri][COLOR=black]   For Each varItem1 In ctlList1.ItemsSelected[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]       For Each varItem2 In ctlList2.ItemsSelected[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]           MsgBox ctlList1.ItemData(varItem1) & " | " & ctlList2.ItemData(varItem2)[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]       Next varItem2[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]   Next varItem1[/COLOR][/FONT]
[/FONT]

 

[pbaldy]

The loop is basically the same, but I don't know that you need nested loops. Mine shows how to build a string, so you'd build that and then have this:

"PlatformDescription In(" & YourString & ") AND " & _

instead of

"PlatformDescription = '" & PlatformDescriptionL & "' AND " & _