filtering subreport from subform (1 Viewer)

[ClaraBarton]

I have both a subform and a subreport that are not connected to the parent form by a field. I keep the form open and populate the fields in the report and that works well but I cannot filter the subreport. I've read a great deal on this and I think this should work:

Private Sub Report_Open(Cancel As Integer)
Dim sfrm As Access.Form
Set sfrm = Forms!frmCalendarMain!frmCalendarWeek.Form

    Reports!rptWeek!rsubWeek.Report.Filter = sfrm!fsubGrocery.Filter
    Reports!rptWeek!rsubWeek.Report.FilterOn = True
End Sub

It doesn't. I tried it on the open event in the subreport. I read that it has to be in the open event and not the load event of the parent report. Neither works.
I had a variable for the Report form but read that doesn't work. (it doesn't) The error I get is #438 object doesn't support this property.
Any suggestions

 

[Gasman]

Repeating the post is not going to help?
I would start with hard coding the name of the subform with all it's hierarchy, and when that is working, try with variables.
As that code is in the report, and I would probably use the load event, you can use Me.

Referencing

Forms: Refer to Form and Subform properties and controls

 

[ebs17]

It should work if the filter is passed to the OpenReport as OpenArgs and then applied to the subreport when opened or loaded.

 

[LarryE]

I have both a subform and a subreport that are not connected to the parent form by a field. I keep the form open and populate the fields in the report and that works well but I cannot filter the subreport. I've read a great deal on this and I think this should work:

Private Sub Report_Open(Cancel As Integer)
Dim sfrm As Access.Form
Set sfrm = Forms!frmCalendarMain!frmCalendarWeek.Form

    Reports!rptWeek!rsubWeek.Report.Filter = sfrm!fsubGrocery.Filter
    Reports!rptWeek!rsubWeek.Report.FilterOn = True
End Sub

It doesn't. I tried it on the open event in the subreport. I read that it has to be in the open event and not the load event of the parent report. Neither works.
I had a variable for the Report form but read that doesn't work. (it doesn't) The error I get is #438 object doesn't support this property.
Any suggestions

You are attempting to filter your sub-report when it opens, bur sub-reports open BEFORE their associated parent report. Even if the sub-report is not linked to the parent by any field, there is nothing for the sub-report to display.

Filter the sub-report when the rptWeek report opens, not when the subWeek report opens. Also, it appears you are attempting to filter the sub-report based upon an entire form, and not any of the reports and forms fields. So obviously the sub-report cannot be filtered. Reports need to be filtered on specific fields and if you are filtering based upon an open form, then the open form needs to have that field as well.

These lines of code do nothing at all:
Dim sfrm As Access.Form
Set sfrm = Forms!frmCalendarMain!frmCalendarWeek.Form

Let's say you have a field named OrderDate on your sub-report and on your open subform frmCalendarWeek. Then your filtering code should be:

Private Peport_Open(Cancel As Integer)
Reports!rptWeek!rsubWeek.Filter = "[OrderDate]= Forms!frmCalendarMain!frmCalendarWeek!OrderDate"
Reports!rptWeek!rsubWeek.FilterOn= True
Reports!rptWeek!rsubWeek.Requery
End Sub

Placed with the rptWeek On Open Event

You are filtering your sub-report named rsubWeek based on your open sub-form named frmCalendarWeek on a field named OrderDate.

 

[ClaraBarton]

I got it working. thank you.

Private Sub Report_Open(Cancel As Integer)
Dim frm As Access.Form
Set frm = Forms("frmCalendarMain")
Set frm = frm.Controls("frmCalendarWeek").Form
Set frm = frm.Controls("fsubGrocery").Form
    Me.Filter = frm.Filter
    Me.FilterOn = True
End Sub