Use a "Dual" multi-select list box to filter a report

[MADDPM]

I frequently use Allen Browne's “Multi-Select List Box to Filter a Report” form and code to select academic years for many of my reports.

However, I would like to have side-by-side lists in my multi-select form: one for academic year and one for Department/Program.

I tried adjusting the code to go to both list boxes – but I’m receiving an error: “Error 424 – object required.”

I’m not strong enough in vba to figure out how to do this.

Many thanks in advance-
Colleen

Private Sub cmdRunEvalsBySupervisorAYDeptReport_Click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   [URL unfurl="true"]http://allenbrowne.com[/URL]
    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. If field type is Text, remove single-quote from beginning of this line.
    strDoc = "rptEvaluationsBySupervisorByAYByDept"

    'Loop through the ItemsSelected in the list box.
    With Me.lstAcademicYear And Me.lstDepartmentProgram
        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 = "[Due_Aug31_for_AY] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Criteria:   " & 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, , "cmdRunEvalsBySupervisorAYDeptReport"
    End If
    Resume Exit_Handler
End Sub

 

[PHV]

Comment out the On Error GoTo instruction to discover which line of code raises the error.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MADDPM]

The troublesome line:

For Each varItem In .ItemsSelected

 

[PHV]

In fact the error is here:
With Me.lstAcademicYear And Me.lstDepartmentProgram

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya MADDPM . . .

[blue]PHV[/blue] is right!

[blue]With Statement: Executes a series of statements on a [purple]single object[/purple] ...[/blue]

Dual objects [blue]raise an error![/blue] as you've shown.
[ol][li] are the lists related in any way?[/li]
[li]Provide an indepth example! ... [/li][/ol]
[blue]Your Thoughts? . . .[/blue]





See Ya! . . . . . .

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

 

[MADDPM]

Hi TheAceMan1 -

No, the lists are not related in any way - both lists originate from tables.

I have no problem going in and adjusting the SQL behind the report to get at what I need - but the select criteria form for users has to be, well, extremely user friendly.

Any suggestions will be greatly appreciated!
-colleen

 

[TheAceMan1]

MADDPM . . .

So ... are you saying you need to [blue]generate reports for each item selected in either or both listboxes?[/blue] ... select 2 items in one and 3 in the other [blue]means 5 reports generated.[/blue] Is that right?

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

See Ya! . . . . . .

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

 

[MADDPM]

TheAceMan1 ....

No, actually, I need one report .... for example: for academic years selected in the AY list:
2005-06
2006-07
2007-08

.... and for the departments selected in the departments list, for example:
Biology
Chemistry
Environmental Sciences
Physics

Hope this helps.
Thanks a bunch!
colleen