rion
asked on
Setting filter on sub report
How do I set a filter on a subreport dynamically at run time? I have a master report with several subreports and in the master report On_Open event proc, I set the subReport source object like this me.child0.SourceObject="My SubReport2 ". No problem so far. But I also want to set the filter property for the sub report and can't figure out how. Any suggestions? Thanks in advance.
Sub objects fill in their details prior to main objects, so if the sub report is based on something in the main report you might have to put the code into the sub's On Open event instead.
A second issue is as Crin says. When you are referin gto the actual contents of a sub form or a sub report, you specify that with the .Report or .Form properties.
Let's look at a form, (less typing to describe... :o] )
If you wanted to Hide the subform named sbFrm, you would use Me!sbFrm.visible - False. But of course that doesn't address the contents of the subform object; it only addresses the control that hols the sub form. If you wanted to hide 'cmdBtn' which wass on the sub form, Me.cmdBtn.Visible = False would not work, because that would be addressing an object on the main form. So how you would address the objects on the sub form need to somehow be distinguished form how you address objects on the main form. That is done through the .Form property of the sub form control: Me!sbFrm.Form!cmdBtn.Visib le = False.
The same syntax is used for reports, sub reports, and the objects on- and properties of sub reports, hence the '.Report' in Chic's answer.
Brian
A second issue is as Crin says. When you are referin gto the actual contents of a sub form or a sub report, you specify that with the .Report or .Form properties.
Let's look at a form, (less typing to describe... :o] )
If you wanted to Hide the subform named sbFrm, you would use Me!sbFrm.visible - False. But of course that doesn't address the contents of the subform object; it only addresses the control that hols the sub form. If you wanted to hide 'cmdBtn' which wass on the sub form, Me.cmdBtn.Visible = False would not work, because that would be addressing an object on the main form. So how you would address the objects on the sub form need to somehow be distinguished form how you address objects on the main form. That is done through the .Form property of the sub form control: Me!sbFrm.Form!cmdBtn.Visib
The same syntax is used for reports, sub reports, and the objects on- and properties of sub reports, hence the '.Report' in Chic's answer.
Brian
ASKER
Here is the on_Open proc for the sub report:
Private Sub Report_Open(Cancel As Integer)
Dim strFilt As String
If Not IsNull([Forms]![OutputPik] ![OutputSu b].[Form]! [SaveHomFi lt]) Then
strFilt = [Forms]![OutputPik]![Outpu tSub].[For m]![SaveHo mFilt]
DoCmd.ApplyFilter , strFilt
End If
End Sub
This works when the report is opened directly but not when it is a sub report. Can you give me specifics on where issue commands to set the sub report properties? Should this be done in the form that opens the main report before the main report is opened, or in the On_open proc of the main report, or in the on_format proc of the main report detail section or where?
I've tried lots of different approaches but can't seem to get access to apply the filter when the report is opened as a sub report.
Private Sub Report_Open(Cancel As Integer)
Dim strFilt As String
If Not IsNull([Forms]![OutputPik]
strFilt = [Forms]![OutputPik]![Outpu
DoCmd.ApplyFilter , strFilt
End If
End Sub
This works when the report is opened directly but not when it is a sub report. Can you give me specifics on where issue commands to set the sub report properties? Should this be done in the form that opens the main report before the main report is opened, or in the On_open proc of the main report, or in the on_format proc of the main report detail section or where?
I've tried lots of different approaches but can't seem to get access to apply the filter when the report is opened as a sub report.
Put this in the Open event of the main report, the one that holds the subreport, (This presumes that the name of the SubReport control is 'SubReport'):
If Not IsNull(Forms!OutputPik!Out putSub.For m!SaveHomF ilt) Then
strFilt = Forms!OutputPik!OutputSub. Form!SaveH omFilt
Me!SubReport.Report.Filter = strFilt
Me!SubReport.Report.Filter On = True
End If
Brian
If Not IsNull(Forms!OutputPik!Out
strFilt = Forms!OutputPik!OutputSub.
Me!SubReport.Report.Filter
Me!SubReport.Report.Filter
End If
Brian
ASKER
Adjusted points from 200 to 300
ASKER
Tried that before, and again. The filter is not applied/active. Here is the Main Report On_Open proc:
Private Sub Report_Open(Cancel As Integer)
Dim strFilt As String
Dim strName As String
strFilt = [Forms]![OutputPik]![Outpu tSub].[For m]![SaveHo mFilt]
strName = "cliHome Search Report"
If Not IsEmpty(varRptNames) Then
For i = 0 To UBound(varRptNames)
Me("child" & i).SourceObject = "report.cli" & varRptNames(i)
If varRptNames(i) = strName Then
Me("child" & i).Report.Filter = strFilt
Me("child" & i).Report.FilterOn = True
End If
Next
End If
End Sub
You see that the sub report's (child0, child1, ... child6) SourceObject is determined here without problem, so I think the basic approach is correct. But for one report ("cliHome Search Report") I have to specify the filter. When this runs as above, the report is created without filtering. It seems that there is some subtlety here that we're missing. Any ideas on what?
Or is there another way to approach this? Could the sub report's filter be set before the main report opens it? Or any other suggestions? Thanks.
Private Sub Report_Open(Cancel As Integer)
Dim strFilt As String
Dim strName As String
strFilt = [Forms]![OutputPik]![Outpu
strName = "cliHome Search Report"
If Not IsEmpty(varRptNames) Then
For i = 0 To UBound(varRptNames)
Me("child" & i).SourceObject = "report.cli" & varRptNames(i)
If varRptNames(i) = strName Then
Me("child" & i).Report.Filter = strFilt
Me("child" & i).Report.FilterOn = True
End If
Next
End If
End Sub
You see that the sub report's (child0, child1, ... child6) SourceObject is determined here without problem, so I think the basic approach is correct. But for one report ("cliHome Search Report") I have to specify the filter. When this runs as above, the report is created without filtering. It seems that there is some subtlety here that we're missing. Any ideas on what?
Or is there another way to approach this? Could the sub report's filter be set before the main report opens it? Or any other suggestions? Thanks.
ASKER
The previous comment that listed the Report_Open proc had a logic error:
strName = "cliHome Search Report" should be
strName = "Home Search Report".
The conditional statements
Me("child" & i).Report.Filter = strFilt
Me("child" & i).Report.FilterOn = True
were never executed with the invalid value in strName.
When corrected, the proc generates this run time error: '2455' ("You entered an expression that has an invalid reference to the property Form/Report. The property may not exist or may not apply to the object you specified").
I've also tried the assignment using this syntax :
Me!child0.Report.Filter = strFilt
with the same error resulting.
Any ideas? Thanks.
strName = "cliHome Search Report" should be
strName = "Home Search Report".
The conditional statements
Me("child" & i).Report.Filter = strFilt
Me("child" & i).Report.FilterOn = True
were never executed with the invalid value in strName.
When corrected, the proc generates this run time error: '2455' ("You entered an expression that has an invalid reference to the property Form/Report. The property may not exist or may not apply to the object you specified").
I've also tried the assignment using this syntax :
Me!child0.Report.Filter = strFilt
with the same error resulting.
Any ideas? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Alright, Thanks for that info.
Does
MySubreport2.Report.Filter
works the way you want?
PS. English is not my native, sorry if I misunderstood something.