How do I use Multi-Select Listbox as report criteria? 3

[LakotaMan]

Hi all,
Using version 2007, I have an unbound form with cbos and a listbox to filter a report. I got this code off a Tek-Tips FAQ by Michael Red many years ago and have used it happily in many applications for my clients. Up to now, I haven’t had any problems. This is, however the first time I’ve ever used a listbox (and a mult-select one at that), and I don’t know how to adapt the code to pick up those multiple values for the strWhere variable (see below). I have searched ‘till I’m blue in the face for Michael’s original FAQ –can’t find it. So am begging the expert advice of you folks in the Forms forum.

Again, the listbox is a multi-select (simple), and here’s the code I’ve used for the cbos:

Private Sub cmdPreview_Click()
 	Dim strWhere As String 

           	If Len(Me.cboNature & "") > 0 Then
                strWhere = strWhere & " AND IssueNature = '" & Me.cboNature & "'"
            End If            
            If Len(Me.cboLocation & "") > 0 Then
                strWhere = strWhere & " AND Location = '" & Me.cboLocation & "'"
            End If            
            If Len(Me.cboCustomer & "") > 0 Then
                strWhere = strWhere & " AND CustomerName = '" & Me.cboCustomer & "'"
            End If            
            If Len(Me.cboCategory & "") > 0 Then
                strWhere = strWhere & " AND ProductCategory = '" & Me.cboCategory & "'"
            End If

        If Len(strWhere & "") = 0 Then
            ' no options selected. Open report with no where condition
            DoCmd.OpenReport "rptComplaintDetails", acViewPreview
        Else
            ' remove first "AND" from where condition and pass across to report
            DoCmd.OpenReport "rptComplaintDetails", acViewPreview, WhereCondition:=Mid(strWhere, 6)
        End If

End Sub

Any help you can give me will, as always, be greatly appreciated.
LM

 

[genomon]

Have a look at the SPLIT() and JOIN() functions to extract multiple selections - the delimiter would be the carriage return character.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

Take a gander at the query forum FAQs. If you can't find an answer come back.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya LakotaMan . . .

In VBA help, have a look at the ItemsSelected property. Its used in a loop to get all selections.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LakotaMan]

I used the link you gave me, Duane, and found a solution, and am now tryiing to adapt the code to work with my situation. I think it will be fine if I can ever get the syntax worked out!

AceMan, thanks for the heads-up, that's essentially what's at the link Duane gave me. So am working on the loop even as we speak.

I'll post back with further questions or report success.

Thanks to all!
LM

 

[LakotaMan]

Hey all!

Got it working, and very pleased am I!
Here's my code snippet:

            'Multi-Select Listbox
            Dim ctlList
            Set ctlList = Me.lstProduct
            
            If Me.lstProduct.ItemsSelected.Count = 0 Then
                'do nothing
            Else
                strWhere = strWhere & " AND Product IN ("
                
                For Each Lmnt In ctlList.ItemsSelected
                   
                  strWhere = strWhere & "'" & ctlList.ItemData(Lmnt) & "',"
         
                Next
                strWhere = strWhere & ")"
            End If

Thanks for pointing me in the right direction Duane and AceMan.
LM

 

[dhookom]

I keep several standard modules in almost every app I create. Here's a sample which includes some functions from Allen Browne.

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
[Green]    'send in a list box control object
    'returns something like:
    '    AND Color in ('Red', 'Blue', 'Green')[/Green]
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
[Green]    'Purpose:   Unselect all items in the listbox.
    'Return:    True if successful
    'Author:    Allen Browne. [URL unfurl="true"]http://allenbrowne.com[/URL]  June, 2006.[/Green]
    Dim varItem As Variant

    If lst.MultiSelect = 0 Then
        lst = Null
    Else
        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next
    End If

    ClearList = True

Exit_ClearList:
    Exit Function

Err_ClearList:
   [Green] 'Call LogError(Err.Number, Err.Description, "ClearList()")[/Green]
    Resume Exit_ClearList
End Function

Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
[Green]    'Purpose:   Select all items in the multi-select list box.
    'Return:    True if successful
    'Author:    Allen Browne. [URL unfurl="true"]http://allenbrowne.com[/URL]  June, 2006.[/Green]
    Dim lngRow As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If

Exit_Handler:
    Exit Function

Err_Handler:
[Green]    'Call LogError(Err.Number, Err.Description, "SelectAll()")[/Green]
    Resume Exit_Handler
End Function

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

Thanks, Duane!

Cogito eggo sum – I think, therefore I am a waffle.

 

[LakotaMan]

Duane,
Loads of thanks for the extras. Also, I found this from one of Aceman's previous posts, seems to clear the listbox as well.

 Me.lstBoxName.RowSource = Me!lstBoxName.RowSource

Any ideas on this vs the loop in Allen's clearing routine?
Thanx again,
LM

 

[dhookom]

I would think that if you reset the Row Source of the list box, it will cause a requery of the list box. This would take longer than looping through the items already retrieved. I'm just guessing. If the Row Source wasn't overly large it shouldn't make any difference.

Duane
Hook'D on Access
MS Access MVP