Use a multi-select list box to filter a report (1 Viewer)

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
I followed the instructions of allen browne (allenbrowne.com/ser-50.html) in order to filter a report with a multi-select list box.

But how do i need to design the report in order to display different columns of a table?
According to step 9 of the instructions linked above i need to change the control source to "=[Report].[OpenArgs]" . But that doesn't seem to work.
Since I'm needing to to display more than 1 column how do i need to reference the different columns for each textbox in the report?

Any help is appreciated! Thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
But how do i need to design the report in order to display different columns of a table?
just drag the columns you want in the report.
the article of mr.browne will only filter 1 column.
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
just drag the columns you want in the report.
the article of mr.browne will only filter 1 column.
do I set a text box for each column with "=[Report].[OpenArgs]" as control source?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
=[Report].[OpenArgs
this will only Show the Category Descriptions of all Category selected from the Listbox.
it has nothing to do with your report?
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
this will only Show the Category Descriptions of all Category selected from the Listbox.
it has nothing to do with your report?
Because in step 9 he created a text box in the report with this as control source.

What I mean is that my report has different text boxes for each column in my table. How do I need to fill (in control source) to display the columns depending and the multi select list box from the form?

I hope this is comprehensibly written
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Jan 23, 2006
Messages
15,364
@Gaccess1
How about creating a mock-up of your report for reviewing format, layout, or content......so we understand your issue in context?
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
1635167616837.png


I want to multi select the project name and display all the different columns based on the selection


And this is how the form looks with the multi select list box:
1635167448607.png


Hope this helps!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
you already has the report, so you only need to Filter it:
on the click of the button that opens your report:
Code:
Private Sub Command2_Click()
    Dim v As Variant
    Dim s As String
    For Each v In Me.List0.ItemsSelected
        s = s & """" & Me.List0.ItemData(v) & ""","
       
    Next
    If Len(s) Then
        s = Left$(s, Len(s) - 1)
        DoCmd.OpenReport ReportName:="theReportName", View:=acViewPreview, WhereCondition:="projekt_name in (" & s & ")"
    else
        DoCmd.OpenReport ReportName:="theReportName", View:=acViewPreview
   
    End If

End Sub
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
I adapted your code but the report is empty. Do i need to change something with the Textboxes in the report?

My previous code looks similar to yours which is adapted from allen browne. The code looks likes this if it helps:

Code:
Private Sub Auswahl_Button_Click()
On Error GoTo Err_Handler
    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 = "Übersicht Projekt test"

    With Me.Projektname_Liste
        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 = "[Projekt_Name] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Categories: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    MsgBox strWhere
    DoCmd.OpenReport strDoc, acViewReport, 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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
if you are willing to upload your db to get a bigger picture?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,175
did you include Project_ID in the Listbox? I think you should:

RowSource: select ID, Project_Name From Projekt;
Column Count: 2
Bound Column: 1
Column Widths: 0; 1"

Now, change this part on your code:
Code:
    If lngLen > 0 Then
        strWhere = "[Projekt_Name] IN (" & Left$(strWhere, lngLen) & ")"
to this:
Code:
If lngLen > 0 Then
        strWhere = "[Projekt_ID] IN (" & Left$(strWhere, lngLen) & ")"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,981
Put a stop on the line of code that opens the report. When the code window opens, print strWHERE and paste it here if you don't see the problem.
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
did you include Project_ID in the Listbox? I think you should:

RowSource: select ID, Project_Name From Projekt;
Column Count: 2
Bound Column: 1
Column Widths: 0; 1"

Now, change this part on your code:
Code:
    If lngLen > 0 Then
        strWhere = "[Projekt_Name] IN (" & Left$(strWhere, lngLen) & ")"
to this:
Code:
If lngLen > 0 Then
        strWhere = "[Projekt_ID] IN (" & Left$(strWhere, lngLen) & ")"

I changed it to "Projekt_ID" but now it wants me to enter a parameter. I assume it is because "Projekt_ID" is not a column in the "Maßnahmen" table.
Then i tried "ID" from the "Projekt" table then this error occurs:
1635518664072.png
it means the datatype is in conflict in the criteria expression
Put a stop on the line of code that opens the report. When the code window opens, print strWHERE and paste it here if you don't see the problem.
I logged the string which is:
1635518852932.png
 

Gaccess1

New member
Local time
Today, 10:47
Joined
Oct 25, 2021
Messages
8
1635519012110.png

Maybe another idea: What do I need to put in the control source in the report

What i tried so far is:
  1. =[Report].[OpenArgs]
  2. the column of the "Maßnahmen" table
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,981
Is project_ID numeric? If so, do not enclose it in quotes. The string should be:
[Project_ID] In (1,2)
 

Users who are viewing this thread

Top Bottom