Subreport filter - The setting you entered isn't valid for this pr

[Guest]

I have a Report in Access 2003 which has a subreport. In the report I want to
dynamically determine a filter for the subreport.
When setting the filter and the FilterOn properties, I get the error above.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim subReportFilter as String
subreportFilter = "... some value"

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.filter = subReportFilter

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.FilterOn = True
End Sub

The error I get is "The setting you entered isn't valid for this property"

How can I filter the subreport.

 

[Allen Browne]

Wozza, I don't believe you are able to set the Filter for a subreport like
that.

If you are trying to tie the subreport to a particular value on the main
report, you can do that by adding another text box name to the
LinkMasterFields and LinkChildFields properties of the subreport control.

If you are trying to filter the subreport to a range of values that matches
the same range for the main report (such as dates in a particular quarter),
you can create a form with text boxes for the limiting values, and then
refer to these text boxes in the criteria of both the main report's query,
and also the subreport's query.

 

[Guest]

Wozza, I don't believe you are able to set the Filter for a subreport like
that.

If you are trying to tie the subreport to a particular value on the main
report, you can do that by adding another text box name to the
LinkMasterFields and LinkChildFields properties of the subreport control.

When you say "add another text box name ..." what do you man?
All I see are the columns of the query in the master form?
I have added another text box to the parent report which has a value in it
that I want to filter by, but there is no way to link this to the child form.

If you are trying to filter the subreport to a range of values that matches
the same range for the main report (such as dates in a particular quarter),
you can create a form with text boxes for the limiting values, and then
refer to these text boxes in the criteria of both the main report's query,
and also the subreport's query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a Report in Access 2003 which has a subreport. In the report I want
to
dynamically determine a filter for the subreport.
When setting the filter and the FilterOn properties, I get the error
above.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim subReportFilter as String
subreportFilter = "... some value"

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.filter
= subReportFilter

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.FilterOn
= True
End Sub

The error I get is "The setting you entered isn't valid for this property"

How can I filter the subreport.

 

[Allen Browne]

Can you explain more about the Filter you are trying to set on the subform?
Was it along the lines of:
SomeField = SomeValue
If so, and you have the SomeValue in the text box on the main form, then you
can add the name of that text box to the LinkMasterFields, and SomeField to
the LinkChildFields.

If that is not what you were trying to do, post an example of the Filter
string you were trying to use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wozza, I don't believe you are able to set the Filter for a subreport
like
that.

If you are trying to tie the subreport to a particular value on the main
report, you can do that by adding another text box name to the
LinkMasterFields and LinkChildFields properties of the subreport control.

When you say "add another text box name ..." what do you man?
All I see are the columns of the query in the master form?
I have added another text box to the parent report which has a value in it
that I want to filter by, but there is no way to link this to the child
form.

If you are trying to filter the subreport to a range of values that
matches
the same range for the main report (such as dates in a particular
quarter),
you can create a form with text boxes for the limiting values, and then
refer to these text boxes in the criteria of both the main report's
query,
and also the subreport's query.

I have a Report in Access 2003 which has a subreport. In the report I
want
to
dynamically determine a filter for the subreport.
When setting the filter and the FilterOn properties, I get the error
above.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
Dim subReportFilter as String
subreportFilter = "... some value"

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.filter
= subReportFilter

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.FilterOn
= True
End Sub

The error I get is "The setting you entered isn't valid for this
property"

How can I filter the subreport.

 

[Guest]

Ok, So the value I wanted to use to filter on was not in the main query. It
is a calculated value (count of how many records are being displayed). I have
managed to get around the problem by using a "=DCount" text box on the main
report, and then using that in the SELECT statement of the subreport SELECT
.... WHERE seq <=
Reports![_RPT_ServiceDiscovery]!txtServiceDatabaseNewRecordsCount.Text;

Previously I was trying to calculate the value in VBA and then apply a
filter to the subreport (unsuccessfully).

Main query was SELECT serviceId, name FROM Service
Subreport 1 was SELECT databaseId, name, serviceId from Database (link
between child and parent was using servideId.
Subreport 2 (where the problem was) was the equivalent of ...
SELECT seq, "" as name from DummyTable WHERE seq <= (SELECT COUNT(*) FROM
Database WHERE serviceId = xxx)
My problem was trying to work out the count based on the serviceId of the
main report and applying that to the second subreport using a filter.

Anyway ... the problem is solved - thanks for the help.

Can you explain more about the Filter you are trying to set on the subform?
Was it along the lines of:
SomeField = SomeValue
If so, and you have the SomeValue in the text box on the main form, then you
can add the name of that text box to the LinkMasterFields, and SomeField to
the LinkChildFields.

If that is not what you were trying to do, post an example of the Filter
string you were trying to use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wozza, I don't believe you are able to set the Filter for a subreport
like
that.

If you are trying to tie the subreport to a particular value on the main
report, you can do that by adding another text box name to the
LinkMasterFields and LinkChildFields properties of the subreport control.

When you say "add another text box name ..." what do you man?
All I see are the columns of the query in the master form?
I have added another text box to the parent report which has a value in it
that I want to filter by, but there is no way to link this to the child
form.

If you are trying to filter the subreport to a range of values that
matches
the same range for the main report (such as dates in a particular
quarter),
you can create a form with text boxes for the limiting values, and then
refer to these text boxes in the criteria of both the main report's
query,
and also the subreport's query.

I have a Report in Access 2003 which has a subreport. In the report I
want
to
dynamically determine a filter for the subreport.
When setting the filter and the FilterOn properties, I get the error
above.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
Dim subReportFilter as String
subreportFilter = "... some value"

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.filter
= subReportFilter

Reports![_RPT_ServiceDiscoveryDatabase]![_RPT_ServiceDiscoveryDatabase_New].Report.FilterOn
= True
End Sub

The error I get is "The setting you entered isn't valid for this
property"

How can I filter the subreport.