MultiSelect listbox

[auerdl]

Multiselect listboxs
I am so close but yet soooo far...

I have a form with combo boxes which display the choices from the boxs in a list box.
If you choose from one of the 7 combo boxes it limits the display in the list box, if you choose another combo box, the listbox display is limited ever further...

It works great, buttttt

I need 4 of the combo boxes to be MULTIselect list boxes.
The boxes that are needed to be the combo boxes are (cboFY, cboCC, cboSigmaStatus and cboProjectType.

The code is as follows:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT FY, CC, ChargeNo, SigmaPlusNo, ProjectType, SigmaStatus, BeltName " & _
"FROM qProjectt WHERE FY like '"
Private Const strSQL2 = "' AND CC like '"
Private Const strSQL3 = "' AND BeltName like '"
Private Const strSQL4 = "' AND ChargeNo like '"
Private Const strSQL5 = "' AND ProjectType like '"
Private Const strSQL6 = "' AND SigmaPlusNo like '"
Private Const strSQL7 = "' AND SigmaStatus like '"
Private Const strSQL8 = "' Order by FY desc;"
Private strSQL As String

Private Const strMsg1 = "Select a product from the list"
Private Const strMsg2 = "Select a FY from the list"

Private Sub cboBeltName_AfterUpdate()
If Me!cboBeltName.Value <> &quot;&quot; Then
Call FillList
' Else
' Me!lblList.Caption = strMsg3
End If
End Sub

Private Sub cboChargeNo_AfterUpdate()
If Me!cboChargeNo.Value <> &quot;&quot; Then
Call FillList
' Else
' Me!lblList.Caption = strMsg3
End If
End Sub

Private Sub cboFY_AfterUpdate()
If Me!cboCC.Value > 0 Then
Call FillList
Else
Me!lblList.Caption = strMsg1
End If
End Sub

Private Sub cboCC_AfterUpdate()
If Me!cboFY.Value <> &quot;&quot; Then
Call FillList
Else
Me!lblList.Caption = strMsg2
End If
End Sub

Private Sub cboProjectType_AfterUpdate()
If Me!cboProjectType.Value <> &quot;&quot; Then
Call FillList
' Else
' Me!lblList.Caption = strMsg3
End If
End Sub

Private Sub cboSigmaPlusNo_AfterUpdate()
If Me!cboSigmaPlusNo.Value <> &quot;&quot; Then
Call FillList
' Else
' Me!lblList.Caption = strMsg3
End If
End Sub

Private Sub cboSigmaStatus_AfterUpdate()
If Me!cboSigmaStatus.Value <> &quot;&quot; Then
Call FillList
' Else
' Me!lblList.Caption = strMsg3
End If
End Sub

Private Sub Form_Activate()
If Me!cboFY.Value <> &quot;&quot; And Me!cboCC.Value > 0 Then
Call FillList
Else
Me!lblList.Caption = strMsg2
End If
End Sub

Private Sub FillList()
strSQL = strSQL1 & Me!cboFY.Value & _
strSQL2 & Me!cboCC.Value & _
strSQL3 & Me!cboBeltName.Value & _
strSQL4 & Me!cboChargeNo.Value & _
strSQL5 & Me!cboProjectType.Value & _
strSQL6 & Me!cboSigmaPlusNo.Value & _
strSQL7 & Me!cboSigmaStatus.Value & _
strSQL8
Me!lstOrders.RowSource = strSQL
Me!lstOrders.Requery
Me!lblList.Caption = &quot;Orders from &quot; & _
Me!cboFY.Value & &quot; for &quot; & _
Me!cboCC.Column(1)
If Me!lstOrders.ListCount = 0 Then
Me!lblList.Caption = &quot;No &quot; & Me!lblList.Caption
End If
End Sub

 

[RickSpr]

I see many problems with this code. First, FillList builds a SQL statement that tests all the fields, regardless of whether any of them may be empty. That should give you an SQL syntax error unless the combo boxes all have a value all the time--in which case why are you testing them for &quot;&quot;?

Second, testing the combo boxes for &quot;&quot; doesn't accomplish anything. If I'm not mistaken, the value of a combo box in which nothing is selected is Null, not &quot;&quot;, so this test will always come out False.

Third, why are you testing with like? The = operator would be much more efficient, unless you actually have substrings like &quot;*xxx*&quot; in your combo boxes.

Fourth, you don't actually need to requery Me!lstOrders. Assigning a new value to its RowSource will automatically requery it.

For the multiselect combo boxes, you should use an IN operator in your SQL statement, and you should append a list of the values, separated by commas and enclosed in parentheses.

Here's some sample code for what you're doing:

(Declarations section)
Dim index As Integer
Dim strFYList As String
Dim strCCList As String
(etc. for each multi-select combo box)

Private Sub cboFY_AfterUpdate()
    strFYList = ""
    For Each index In cboFY.SelectedItems
        strFYList = strFYList & cboFY.ItemData(index) & ","
    Next index
    If Len(strFYList) > 0 Then 
        strFYList = "(" & Left$(strFYList, Len(strFYList) - 1) & ")"
    End If
    Call FillList
End Sub

Private Sub FillList()
    Private Const strSQL0 = "SELECT FY, CC, ChargeNo, SigmaPlusNo, ProjectType, SigmaStatus, BeltName " & _ 
    "FROM qProjectt WHERE TRUE"
    Private Const strSQL1 = " AND FY IN "
    Private Const strSQL2 = " AND CC IN "
    Private Const strSQL3 = " AND BeltName = "

    strSQL = strSQL0
    If Len(strFYList) > 0 Then strSQL = strSQL & strSQL1 & strFYList
    If Len(strCCList) > 0 Then strSQL = strSQL & strSQL2 & strCCList
    If Not IsNull(cboBeltName) Then strSQL = strSQL3 & "'" & BeltName & "'"
    (etc.)
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[auerdl]

Thank you for your input.

I will try it this weekend.

deb