Vba

[hehrerh]

I have some code on a form that is designed to apply filters to the report "rptTaxReturnReport" based on selections of "Developer" and "Phase" from two list boxes on the same form.

The code is supposed to build a filter string "strFilter" and apply it to the report when button "btnApplyFilter" is clicked.

I have a second button designed to clear the list boxes and reset the filters on the form.

Currently, neither button does anything when I press it. The filters are not applied when "btnApplyFilter" is clicked, and they are not reset when "btnRemoveFilter" is clicked.

Please take a look at my code and see if anything jumps out. All objects on the form are named as shown in the code - I have checked several times over.

Thanks!

Private Sub btnApplyFilter_Click()




Dim varItem As Variant
Dim strPhase As String
Dim strDeveloper As String
Dim strFilter As String

' Check that the report is open


If SysCmd(acSysCmdGetObjectState, acReport, "rptTaxReturnReport") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If

' Build criteria string from listPhase listbox


For Each varItem In Me.listPhase.ItemsSelected
strPhase = strPhase & "," & Me.strPhase.ItemData(varItem)
Next varItem
If Len(strPhase) = 0 Then
strPhase = "Like '*'"
Else
strPhase = Right(strPhase, Len(strPhase) - 1)
strPhase = "IN(" & strPhase & ")"
End If

' Build criteria string from listDeveloper listbox


For Each varItem In Me.listDeveloper.ItemsSelected
strDeveloper = strDeveloper & "," & Me.strDeveloper.ItemData(varItem)
Next varItem
If Len(strDeveloper) = 0 Then
strDeveloper = "Like '*'"
Else
strDeveloper = Right(strDeveloper, Len(strDeveloper) - 1)
strDeveloper = "IN(" & strDeveloper & ")"
End If

' Build filter string
strFilter = "[Phase] " & strPhase & _
" AND [Developer] " & strDeveloper


' Apply filter to report


With Reports![rptTaxReturnReport]
.Filter = strFilter
.FilterOn = True


End With


End Sub
Private Sub btnRemoveFilter_Click()


' Remove filter and sort from report
On Error Resume Next
With Reports![rptTaxReturnReport]
.FilterOn = False
End With
On Error GoTo 0


' Reset form to original values


For Each varItem In Me.listDeveloper.ItemsSelected
Me.listDeveloper.Selected(varItem) = False
Next varItem
For Each varItem In Me.listPhase.ItemsSelected
Me.listPhase.Selected(varItem) = False
Next varItem

End Sub

[Minty]

I would add some debug.print strPhase and strDeveloper statements in the code and see what the immediate window tells you.

In fact I would simply open the report using your string as a where condition. I suspect from memory that a report filter can only be applied on load/open.

[ssanfu]

I looked at your code.... you must not have these two lines at the top of every code page:

Option Compare Database
Option Explicit

I have never seen where a filter was applied to a report AFTER it was opened.

As Minty said, I would open the report with the WHERE clause of the OPENREPORT command.

In the routine "btnRemoveFilter_Click", "varItem" has not been declared and causes an error.
As far as the btnApplyFilter_Click routine, you have undefined names in the code:

Private Sub btnApplyFilter_Click()
    Dim varItem As Variant
    Dim strPhase As String
    Dim strDeveloper As String
    Dim strFilter As String

    ' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptTaxReturnReport") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If

    ' Build criteria string from listPhase listbox
    For Each varItem In Me.listPhase.ItemsSelected
        strPhase = strPhase & "," & Me.strPhase.ItemData(varItem)    '<< NO such control
    Next varItem
    If Len(strPhase) = 0 Then
        strPhase = "Like '*'"
    Else
        strPhase = Right(strPhase, Len(strPhase) - 1)
        strPhase = "IN(" & strPhase & ")"
    End If

    ' Build criteria string from listDeveloper listbox
    For Each varItem In Me.listDeveloper.ItemsSelected
        strDeveloper = strDeveloper & "," & Me.strDeveloper.ItemData(varItem)    '<< NO such control
    Next varItem
    If Len(strDeveloper) = 0 Then
        strDeveloper = "Like '*'"
    Else
        strDeveloper = Right(strDeveloper, Len(strDeveloper) - 1)
        strDeveloper = "IN(" & strDeveloper & ")"
    End If

    ' Build filter string
    strFilter = "[Phase] " & strPhase & " AND [Developer] " & strDeveloper


    ' Apply filter to report
    With Reports![rptTaxReturnReport]
        .Filter = strFilter
        .FilterOn = True
    End With

End Sub

[Micron]

Filtering an open form from a combo selection is quite normal I would think. Filtering an open report isn't that unusual to me.
I think one problem is that the string values are not surrounded by quotes. You're creating IN(a,b,c). You should be creating IN('a','b','c').