CODEWITHSUNDEEP
ms-accessvba
Codingrookie
Codingrookie

Reputation: 43

Filtering Report

I have created a form hoping to give the user the option filter a report. The form has list boxes to select options, a filter button and a clear button. In the bottom of the form I added the subreport so it can update based on the filter criteria. How can I call out the subreport in the code so it filters? It works if I do it directly on the report with Report![List] but it wont update on the form subreport. I'm also not getting all the records showing when filtered because records that have one of the two criteria blank won't appear due to the If Len(strSubsystem) = 0 Then strSubsystem = "Like '*'" statement. Is there another statement I can use besides "like '*'" to have all records show.

Here's the code

Private Sub cmdApplyFilter_Click()
     Dim varItem As Variant
     Dim strSubsystem As String
     Dim strStatus As String
     Dim strFilter As String

' Build criteria string from subsystem listbox
    For Each varItem In Me.lstSubsystems.ItemsSelected
         strSubsystem = strSubsystem & ",'" & Me.lstSubsystems.ItemData(varItem) _
         & "'"
     Next varItem
     If Len(strSubsystem) = 0 Then
         strSubsystem = "Like '*'"
     Else
         strSubsystem = Right(strSubsystem, Len(strSubsystem) - 1)
         strSubsystem = "IN(" & strSubsystem & ")"
     End If
' Build criteria string from status listbox
    For Each varItem In Me.lstStatus.ItemsSelected
         strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) _
         & "'"
     Next varItem
     If Len(strStatus) = 0 Then
         strStatus = "Like '*'"
     Else
         strStatus = Right(strStatus, Len(strStatus) - 1)
         strStatus = "IN(" & strStatus & ")"
     End If

' Build filter string
    strFilter = "[Subsystem] " & strSubsystem & _
                 " AND [Status] " & strStatus

' Apply the filter and switch it on
    With Forms![Filter]
         .Filter = strFilter
         .FilterOn = True
     End With
End Sub

Upvotes: 1

Views: 1053

Answers (2)

HansUp
HansUp

Reputation: 97111

The first thing you need to do is find the name of the subform control which contains the report. If you have difficulty finding that name when the form is in Design View, inspect the form's controls from the Immediate window. (Ctrl+g will take you to the Immediate window.)

Here is an example with my form ...

for each ctl in Forms!Form10.controls : ? ctl.name, TypeName(ctl) : next
subReport     SubForm
Label0        Label
txtSort          TextBox
Label2        Label

So my subform control is named subReport.

Once I have that name, I can switch the form back to Form View and work with the Filter property of the Report contained in the subform control ...

Forms!Form10!subReport.Report.filter = "id=5"
Forms!Form10!subReport.Report.filteron = true

The report was immediately filtered to display only the one matching row.

Afterward, I switch off the filter, but the report display did not change until I called Requery (on the subform control) ...

Forms!Form10!subReport.Report.filteron = false
Forms!Form10!subReport.requery

If I wanted to do the the filtering from the click event of a command button which was also contained on the parent form, I could shortcut to the control name ...

Private Sub cmdApplyFilter_Click()
    Me!subReport.Report.Filter = "id=1"
    Me!subReport.Report.FilterOn = True

Upvotes: 0

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

There shouldn't be a need for you to check whether the report is open if it's embedded as a subform and always appears when the main form is open. Subforms open automatically when the parent form is opened, so unless there is something closing it you shouldn't need to check. When referencing the subform you should be using the [Forms]! collection, not the [Reports]! collection. Check out this page for help on referencing subform objects.

I'm not totally clear on the structure of your form, but the reference would probably be: 

[Forms]![Filter]!SubFormControlNameHere.Report.Filter

Upvotes: 1

Related Questions