Hi,
I have a report bound to a Teams table with Team & TeamID fields (TeamID = autonumber). The report has several subreports each linked to the report on the TeamID field. Majority of the subreports are crosstab queries grouped by TeamID and a user-set date range, the rest are simple select queries grouped by TeamID and date range. For eg, one subreport returns the number of incoming calls categorized by type for each team.
Main report
Team TeamID
A 1
B 2
C 3
Subreport
TeamID CallTypeA CallTypeB
1 5 1
2 10 2
3 3 7
I have a form that lets the user select date range (from & to date fields) and a multi select listbox that will let them select specific teams for the report. I tried Allen Browne's code (pasted below) to filter the report based on the listbox selected items, but that only returns data for the first item selected regardless of how many I've selected. I guess I'd have to check each TeamID in the query against the items selected, and add the values for each TeamID to the next match, but I'm completely lost as to how to achieve that with code. Please help!
**********************************************************
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004.
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_StatsAnnual"
'Loop through the ItemsSelected in the list box.
With Me.lst_Team
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
' [UserName] = CStr([TeamID]). The Microsoft support site
' had something about a subreport not displaying data properly if linked to
' the main report by an autonumber field. It suggested converting the field to
' a string.
strWhere = "[UnitName] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Teams: " & 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
*******************************************************
I have a report bound to a Teams table with Team & TeamID fields (TeamID = autonumber). The report has several subreports each linked to the report on the TeamID field. Majority of the subreports are crosstab queries grouped by TeamID and a user-set date range, the rest are simple select queries grouped by TeamID and date range. For eg, one subreport returns the number of incoming calls categorized by type for each team.
Main report
Team TeamID
A 1
B 2
C 3
Subreport
TeamID CallTypeA CallTypeB
1 5 1
2 10 2
3 3 7
I have a form that lets the user select date range (from & to date fields) and a multi select listbox that will let them select specific teams for the report. I tried Allen Browne's code (pasted below) to filter the report based on the listbox selected items, but that only returns data for the first item selected regardless of how many I've selected. I guess I'd have to check each TeamID in the query against the items selected, and add the values for each TeamID to the next match, but I'm completely lost as to how to achieve that with code. Please help!
**********************************************************
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004.
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_StatsAnnual"
'Loop through the ItemsSelected in the list box.
With Me.lst_Team
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
' [UserName] = CStr([TeamID]). The Microsoft support site
' had something about a subreport not displaying data properly if linked to
' the main report by an autonumber field. It suggested converting the field to
' a string.
strWhere = "[UnitName] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Teams: " & 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
*******************************************************
