Using a multi-select list box to filter a report
Fran I recently tried to use code for "Use a multi-select list box to filter a report" from Allen Browne in my database. I was able to add the code and adapt it to my needs, however I am getting an error. I click Ok and the report opens. I look in the design view and the filter is there but the IN equals "" [it is blank, the values are not in there] The description that appears on the report, which is the strDescrip is correct. I can see the descriptions of the items I have selected but the criteria is not picking them up. I am using Access 2002 - 2003. My criteria is a text field. Table name is: Tbl_Interns Criteria field is: Work State Location Report Name Is: Rpt_WorkingStates On my report I added a text field called: TxtReportCriteria And in the control source, I added:=[Report].[OpenArgs] Here is a sample of the code I am using. 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. //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_WorkingStates" '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 = "Tbl_Interns.[Work State Location]IN [" & Left$ [strWhere, lngLen] & "]" lngLen = Len[strDescrip] - 2 If lngLen 0 Then strDescrip = "Selected Work States: " & 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 Chủ Đề |