Change filter on sub-report

[tedmi]

I can't change the filter string of a sub-report in its Open event.
Attempting to set either Me.Filter or Me.FilterOn produces the message:
The setting you entered isn't valid for this property

Thanks for any help.

 

[Marshall Barton]

I can't change the filter string of a sub-report in its Open event.
Attempting to set either Me.Filter or Me.FilterOn produces the message:
The setting you entered isn't valid for this property

You probably can do that, but with some significant
restrictions. The code must be in the subreport's Open
event procedure (not in the main report).

Furthermore, it can only be done once. If the subreport
appears multiple times in the main report's print or
preview, then you use must add code to make sure it only
runs once.

Personally, I have had so many problems with the Filter
property that I only set the subreport's RecordSource (with
the same restrictions).

 

[tedmi]

The error I'm getting occurs in the Open event of the subreport. I've come to
the same conclusion as you did: change the sub's recordsource.

Thanks for the response.

 

[Evi]

Here's the code which I use to filter my subform to the current month (I
have a calculated field called Mnth in the subform.. I use the On Open Event
of the *Main* form

Private Sub Form_Open(Cancel As Integer)
Dim MyMonth As Integer
Dim MySub As Control
Dim frm As Form
'maximise form
DoCmd.Maximize
'filter form to show current month
MyMonth = Month(Date)
Me.cboMonth = MyMonth
Set MySub = Me.FrmJobsExpensesV2Sub
'the name of my subform control
MySub.SetFocus
'MySub.Form.FilterOn = False
'Remove previous Filters
MySub.Form.Filter = "[Mnth] =" & MyMonth
MySub.Form.FilterOn = True
Set MySub = Nothing
DoCmd.GoToControl "FrmJobsExpensesV2Sub"
DoCmd.GoToRecord , , acNewRec

End Sub

Evi

 

[Evi]

Duh, just realized that you were talking about a subreport, not a form. Must
stop answering posts late at night.
Evi

Here's the code which I use to filter my subform to the current month (I
have a calculated field called Mnth in the subform.. I use the On Open Event
of the *Main* form

Private Sub Form_Open(Cancel As Integer)
Dim MyMonth As Integer
Dim MySub As Control
Dim frm As Form
'maximise form
DoCmd.Maximize
'filter form to show current month
MyMonth = Month(Date)
Me.cboMonth = MyMonth
Set MySub = Me.FrmJobsExpensesV2Sub
'the name of my subform control
MySub.SetFocus
'MySub.Form.FilterOn = False
'Remove previous Filters
MySub.Form.Filter = "[Mnth] =" & MyMonth
MySub.Form.FilterOn = True
Set MySub = Nothing
DoCmd.GoToControl "FrmJobsExpensesV2Sub"
DoCmd.GoToRecord , , acNewRec

End Sub

Evi

I can't change the filter string of a sub-report in its Open event.
Attempting to set either Me.Filter or Me.FilterOn produces the message:
The setting you entered isn't valid for this property

Thanks for any help.