Report form with filter

[d9pierce1]

Hi all,
I created this report form so i can select reports and open them... I added a filter lstbox from allen brown and I am wondering if it can be altered so that
it would be able to filter from my lstbox selection of reports instead of just one. Currently I have apply filter button which works, but only will open one report
The goal is to be able to filter all the reports based on selection of my LEFT listbox, See below.

Private Sub Ctl_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 = "BusinessByIndustry"


    'Loop through the ItemsSelected in the list box.
    With Me.LstIndustryType
        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 = "[BusinessHelperID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "IndustryType: " & 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


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

[Welshgasman]

Well does the data on the right have a field which identifies which category on the left it belongs to?

[Gicu]

Do you want to open more than one report at the time or you just want to select the one report to open in the left listbox? Either way you need to make the strDoc take its value from the listbox instead of being hard-coded like you have it now:

 With Me.LstReports
        For Each varItem In .ItemsSelected
           strDoc=.Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden    
	   'now open the report
	   DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
	Next
 End With

Cheers,

[Welshgasman]

Do you want to open more than one report at the time or you just want to select the one report to open in the left listbox? Either way you need to make the strDoc take its value from the listbox instead of being hard-coded like you have it now:

 With Me.LstReports
        For Each varItem In .ItemsSelected
           strDoc=.Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden    
       'now open the report
       DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    Next
 End With

Cheers,

I read it as selecting a category from the left listbox, which would limit the entries in the right listbox?

[d9pierce1]

Yes, thanks Welshgasman! It does

[d9pierce1]

Thanks Again Gicu,
I just want one at a time open, just one button to open them as needed and to be able to filter if desired.
Thanks

[Welshgasman]

Yes, thanks Welshgasman! It does

So in the afterupdate event of the left listbox, amend the source for the right listbox?
However you would need a field in that source to know what to filter on?

[d9pierce1]

Worked like a charm! Thank you all so much
that just makes my day
Dave