none
Filtering a Report based on Multi-List Values RRS feed

  • Question

  • Hi all, 

    Hoping someone can help me out. I am trying to filter a report based on the values in a multi list box. Can someone tell me what is wrong with the code below? Any help will be much appreciated. 

    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
        'Purpose:  Open the report filtered to the items selected in the list box.
        'Author:   Allen J Browne, 2004.   http://allenbrowne.com
        Dim varItem As Variant      'Selected items
        Dim strWhere As String      'String to use as WhereCondition
        Dim strDescrip As String    'Description of WhereCondition
        Dim lngLen As Long          'Length of string
        Dim strDelim As String      'Delimiter for this field type.
        Dim strDoc As String        'Name of report to open.
        
        'strDelim = """"            'Delimiter appropriate to field type. See note 1.
        strDoc = "rpt_01_Lesson Learned Report"

        'Loop through the ItemsSelected in the list box.
        With Me.lstCategory
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
                    'Build up the filter from the bound column (hidden).
                    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                    'Build up the description from the text in the visible column. See note 2.
                    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
                End If
            Next
        End With
        
        'Remove trailing comma. Add field name, IN operator, and brackets.
        lngLen = Len(strWhere) - 1
        If lngLen > 0 Then
            strWhere = "[Impact_ID] IN (" & Left$(strWhere, lngLen) & ")"
            lngLen = Len(strDescrip) - 2
            If lngLen > 0 Then
                strDescrip = "Impacts: " & Left$(strDescrip, lngLen)
            End If
        End If
        
        'Report will not filter if open, so close it. For Access 97, see note 3.
        If CurrentProject.AllReports(strDoc).IsLoaded Then
            DoCmd.Close acReport, strDoc
        End If
        
        'Omit the last argument for Access 2000 and earlier. See note 4.
        DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

    Exit_Handler:
        Exit Sub

    Friday, April 15, 2016 4:18 PM

Answers

  • The first error I get is that it prompts me to enter the Impact_ID even though it should be getting the value from the list box.

    I'm not sure what you mean by that.  A multi-select list box has no value, only an ItemsSelected collection, which is a collection of variants, each pointing to a selected row.  Impact_ID must be a column in the report's recordset.



    Ken Sheridan, Stafford, England

    • Marked as answer by JLB43 Monday, April 18, 2016 4:07 PM
    Friday, April 15, 2016 5:26 PM

All replies

  • Apart from the lack of any code in the error handler and the missing End Sub line, nothing that I can see at first sight.  What problem are you experiencing with it?

    For examples of methods to restrict a report by means of a multi-select list box amongst other things you might like to take a look at MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    Friday, April 15, 2016 4:51 PM
  • Thank you for responding, I will take a look at your database. 

    The first error I get is that it prompts me to enter the Impact_ID even though it should be getting the value from the list box. Second, when the report is displayed it is blank.

    Thank you!

    Friday, April 15, 2016 5:17 PM
  • The first error I get is that it prompts me to enter the Impact_ID even though it should be getting the value from the list box.

    I'm not sure what you mean by that.  A multi-select list box has no value, only an ItemsSelected collection, which is a collection of variants, each pointing to a selected row.  Impact_ID must be a column in the report's recordset.



    Ken Sheridan, Stafford, England

    • Marked as answer by JLB43 Monday, April 18, 2016 4:07 PM
    Friday, April 15, 2016 5:26 PM
  • Thank you. I am embarrassed to say it, but the whole issue was not having that field in my query (I assumed I did)....

    Again, thank you so much!

    Friday, April 15, 2016 8:01 PM
  • So as I am new to this.. Can someone help me with adding multiple fields. I also need to be able to select a project (which is a text field) and the Irgun field which is a number.

    Any help will be greatly appreciated.


    Janet Bonelli


    • Edited by JLB43 Monday, April 18, 2016 4:09 PM
    Monday, April 18, 2016 4:09 PM