Filtering a Subreport

[Can]

I create a filter string that filters a report perfectly.
I want that same filter string to filter an unbound
subreport. The subreport is unbound because of counting.

I found some help on another board that said to do this in
the subreport:

Private Sub ReportHeader_Print(Cancel As Integer,
PrintCount As Integer)

Me.Filter = Me.Parent.Filter

End Sub

I get the following error message
Run-time error '2101'
The setting you entered isn't valid for this property

Any thoughts on how I can fix it?

Candace

 

[Marshall Barton]

I create a filter string that filters a report perfectly.
I want that same filter string to filter an unbound
subreport. The subreport is unbound because of counting.

I found some help on another board that said to do this in
the subreport:

Private Sub ReportHeader_Print(Cancel As Integer,
PrintCount As Integer)

Me.Filter = Me.Parent.Filter

End Sub

Sorry, Can, but that doesn't make sense. A Filter only
applies to the (sub)report's RecordSource, so, if the report
is unbound, it doesn't have a record source and therefore a
filter is meaningless.

Maybe you have somthing more going on that would help us
understand what you're dealing with??

 

[Can]

Whoops there is a record source / bound field in the
subreport. I mean there is no link child field and no
link master field to link the subreport to the report. I
want to use the same filter string in the subreport as the
report.

 

[Marshall Barton]

In my experience, the Filter property is more than a litlle
problematic so I avoid it like the plague.

Generally, I try to use form controls as parameters in the
record source query's criteria. If the criteria are too
complex for this approach, then I use the open event of the
(sub)report I want to filter (in your case, both of them) to
set the report's record source to a constructed SQL
statement.

I know that's pretty vague and may not be much help to you.
If you need additional assistance, please provide more
specific information about the criteria you need to use in
your filter and maybe we could figure something out.

 

[Can]

Normally I use form parameters too but in this case the
filtering is too complex for that.

I redid the counts in the report and they all work except
one. Picture a store with a sales table and an inventory
table, then there is an additional table to link each sale
to the inventory list and in that linker table there is a
Quantity field.

I have a report that counts the items sold and displays
the quantity. I want a summary at the bottom saying the
number of sales and the number of items sold. The number
of items sold is no problem but I can't calculate the
number of sales. Is there a way I can do this without
using a subreport? Presently I am getting the count of
Sales ID but because I have the linker table in my record
source I get the total records from the linker table and
the count is the number of items purchased.

-----Original Message-----
In my experience, the Filter property is more than a litlle
problematic so I avoid it like the plague.

Generally, I try to use form controls as parameters in the
record source query's criteria. If the criteria are too
complex for this approach, then I use the open event of the
(sub)report I want to filter (in your case, both of them) to
set the report's record source to a constructed SQL
statement.

I know that's pretty vague and may not be much help to you.
If you need additional assistance, please provide more
specific information about the criteria you need to use in
your filter and maybe we could figure something out.
--
Marsh
MVP [MS Access]

Whoops there is a record source / bound field in the
subreport. I mean there is no link child field and no
link master field to link the subreport to the report. I
want to use the same filter string in the subreport as the
report.
this
in therefore
a

.

 

[Can]

I wrote a little module to set a variable to 1 if it is
not like the last value, otherwise it is zero and then I
sum the total of the variable.

Candace

 

[Marshall Barton]

I wrote a little module to set a variable to 1 if it is
not like the last value, otherwise it is zero and then I
sum the total of the variable.

I don't like this idea. Generally, any event procedure that
tries to keep track of a value from one detail to another is
going to run into trouble because the events can be
triggered multiple times in a semi random order as Access
has to back up and redo some activities because of
KeepTogether, CanGrow, etc.

 

[Marshall Barton]

Normally I use form parameters too but in this case the
filtering is too complex for that.

I redid the counts in the report and they all work except
one. Picture a store with a sales table and an inventory
table, then there is an additional table to link each sale
to the inventory list and in that linker table there is a
Quantity field.

I have a report that counts the items sold and displays
the quantity. I want a summary at the bottom saying the
number of sales and the number of items sold. The number
of items sold is no problem but I can't calculate the
number of sales. Is there a way I can do this without
using a subreport? Presently I am getting the count of
Sales ID but because I have the linker table in my record
source I get the total records from the linker table and
the count is the number of items purchased.

It sounds like all you need is to use a counter text box in
the SalesID group header or footer section. Add a text box
named txtSalesCounter to the group header/footer, set its
control source expression to =1 and RunningSum property to
Over All. Then the report footer can display the number of
sales in a text box with the expression =txtSalesCounter