Filter Sub Report

[CLSWL]

I have a report with two subreports.
I have a form that filters the main report.
How do filter one of the sub reports using vb?

ex: main report lists projects with their summary.
subreport1 lists tasks created by joe
subreport2 lists tasks created by mike

DoCmd.OpenReport MainReport, acViewPreview, , strFilter, acWindowNormal
Wll only filter the main report.

How do i filter the main report so that i can show only task1 created by joe

----Project Name-------
----Summary-----------
-------task1 created by joe------

 

[Marshall Barton]

I have a report with two subreports.
I have a form that filters the main report.
How do filter one of the sub reports using vb?

ex: main report lists projects with their summary.
subreport1 lists tasks created by joe
subreport2 lists tasks created by mike

DoCmd.OpenReport MainReport, acViewPreview, , strFilter, acWindowNormal
Wll only filter the main report.

How do i filter the main report so that i can show only task1 created by joe

----Project Name-------
----Summary-----------
-------task1 created by joe------

Normally, you should use the Link Master/Child properties of
the subreport control for this kind of thing. Linking on
the Task field should be straightforward. To link to joe's
data, add a text box named txtLinkJoe to the main report
section and set its control source to ="joe" or whatever
value is used to identify joe. The properties would then be
somthing like:
Link Master TaskID;txtLinkJoe
LinkChild TaskFK;createdby


OTOH, why not just group the subreport on the createdby
field and do the whole thing in one subreport?

 

[CLSWL]

Ok let me clarify...

The subreports has don't have ANY relationship to one another except that
they both are apart of the same project.

I can filter the main report. I need to filter the subreport using vb.

Lets say as a user i know task1, task2 and task3 created by joe. I don't
know what projects the tasks are associated with.

Lets also say that ProjectABC has task1 created by joe, task5 created by
mike, and task7 created by mike. Project DEF has task2 created by joe, task3
created by joe and task9 created by mike. ProjectGHI has task21 created by
joe.

As i user, If i select task1, task2 and task3 created by joe, i expect to
see the following:

---ProjectABC----
---Summary-----
-task1 created by joe-
-------------------

---ProjectDEF----
---Summary-----
-task2 created by joe-
-task3 created by joe-
-------------------

-------------------End of Report------------------


How do i do that?

 

[Marshall Barton]

The subreports has don't have ANY relationship to one another except that
they both are apart of the same project.

I can filter the main report. I need to filter the subreport using vb.

Lets say as a user i know task1, task2 and task3 created by joe. I don't
know what projects the tasks are associated with.

Lets also say that ProjectABC has task1 created by joe, task5 created by
mike, and task7 created by mike. Project DEF has task2 created by joe, task3
created by joe and task9 created by mike. ProjectGHI has task21 created by
joe.

As i user, If i select task1, task2 and task3 created by joe, i expect to
see the following:

---ProjectABC----
---Summary-----
-task1 created by joe-
-------------------

---ProjectDEF----
---Summary-----
-task2 created by joe-
-task3 created by joe-
-------------------

Maybe you are still oversimplifying. Not only does your
example output not include a second subreport, it does not
even demonstrate a need for any subreports. That output can
be more easily achieved by using a single report that groups
on the project field. The report's record source query
could be like:

SELECT tblprojects.id,
tbltasks.taskid,
tbltasks.reatedby
FROM tblprojects INNER JOIN tbltasks
ON tbltasks.projectid = tblprojects.id
WHERE . . .

The reason I am not addressing your vba filter idea, is
because, except for Link Master/Child, there is no way to
modify a subreport's record source dataset after the report
begins processing data. Because you must provide the filter
criteria so early, you might as well just build the criteria
into the subreport's record source query. At this point
it's usually easier to review the need for a subreport and
try to find a way to avoid it.

There's still the question of how to filter a query to the
specified tasks. How that could be done depends on what
mechanism you have set up for users to enter/select tasks.

For example, if you have a form with a list box of tasks,
then loop throught the list box's ItemsSelected collection
and concatenate the items to form a string like
:task1:task2:task3: which is stuffed into a hidden text box
on the form. The query's WHERE clause could then be

Forms!theform.thetextbox Like ":" & tbltasks.taskid & ":"