subreport filter from Main Report

[dpimental]

I have a Main Report with several subreports.
If I have main report called "MAIN" and sub reports called ...

sub1
sub2
sub3

I am not tying the subreport to the main report in any way. The main report is a container for the subreports.

So, when I open the Main report from code, how do I set filters for each subreport? Normally, it would be something like this ...

DoCmd.OpenReport strReport, acViewPreview, , strFltr

So, how can I programmatically set the filter for each subreport?

David Pimental
(US, Oh)

 

[Remou]

You could include a hidden field on the main report which would hold a value. You could use OpenArgs. You could build the Record Source for the subforms programmatically.

 

[dpimental]

I thought about that; but each subreport has a different field that I want to filter.

I could still use the openargs though?

David Pimental
(US, Oh)

 

[Remou]

Yes. For example:

OpenArgs: "1,A,75"

astrArgs=Split(Openargs,",")

Me.Sub2.Form.Filter="Blah='" & astrArgs(1) & "'"

 

[dpimental]

When I open the report, how do I set the OpenArgs?

Is "OpenArgs" a reserved word, which I just have to reference in the split function?

David Pimental
(US, Oh)

 

[dpimental]

Also, can OpenArgs be used for both reports and forms?

David Pimental
(US, Oh)

 

[Remou]

Oops

You said report.

OpenArgs are for forms:
[tt]DoCmd.OpenForm "frmA", , , , , , "1,A,75"[/tt]

For a report it is probably best to build the query that the subreport is based on:

Dim strSQL As String
Dim qdf As DAO.QueryDef

    strSQL = "Select * From tblA Where TextField='" _
    & Me.txtText & "'" 
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If

 

[PWise]

in A97 reports dont have openargs, but from ay2k it does have open args

 

[Remou]

pwise
My version of Access 2000 does not have OpenArgs for OpenReport, hence the 'oops'.

 

[dpimental]

Neither does mine.

Let me give you a little more detail (just what you wanted, right).

I have a Main report with 3 subreports. There is no connection between them . The main report is only a container.

So, for each subreport, there is an underlying query that is the recordsource of the main report.

Some times I open the main report with all records for all subreports and sometimes I open the main report with the subreports having a null "actual" date. But here's the kicker. Each subreport has a different "actual" date field in a different query.

What do you think of this.

I call a suprocedure that will open the report. but a pass it an argument to determine whether it needs to filter all the subreports.

something like ...

Sub opnFilteredSubs(blnFltr As Boolean)
If blnFltr = True Then
Dim R As Report_MAIN
R.sub1.Report.Filter = "[eadate] Is Null"
R.sub2.Report.Filter = "[sadate] Is Null"
R.sub3.Report.Filter = "[fadate] Is Null"
End If
End Sub

If I do filter the subreports, each subreport will be filtered the same each time.

What do you think?

David Pimental
(US, Oh)

 

[Remou]

It is horribly difficult to filter subreports at run-time. Access is very fussy indeed about the events that you use for reports. All this means that it is far easier to tamper with a query or reference a form, rather than trying to filter subreports.

 

[dpimental]

That stinks.

Maybe there is a better solution.

what about changing the source of the subreports on opening the main form.

such as ...

Sub opnFilteredSubs(blnFltr As Boolean)
Dim R As Report_MAIN
DoCmd.OpenReport "MAIN3", acViewPreview
If blnFltr = True Then
R.sub1.Report.RecordSource = "s1_query2"
R.sub2.Report.RecordSource = "s2_query2"
R.sub3.Report.RecordSource = "s3_query2"
else
R.sub1.Report.RecordSource = "s1_query1"
R.sub2.Report.RecordSource = "s2_query1"
R.sub3.Report.RecordSource = "s3_query1"
End If
Reports!MAIN3.Requery
End Sub

What about that?

David Pimental
(US, Oh)

 

[Remou]

You may get away with setting the RecordSource, however, you will have to use the Open event of the subform, not the events of the report.

Why do you dislike the idea of building a query?

 

[dpimental]

I don't dislike the idea.

It is still a matter of work, isn't it?

If I have 3 subreports and I need to filter them in different ways, even if it's only 2 different ways, I see 2 options. This is especially true since my filter for each subreport will be the same each time.

1.
I could either have 2 different main reports and 6 different subreports. That way, I already have the records filtered. If the records need to be filtered, I choose Main3a, other wise I choose Main3b.

2.
I somehow dynamically filter the subreports on-the-fly.

But how would you build the query (on-the-fly)?

David Pimental
(US, Oh)

 

[Remou]

As per my post above

Dim strSQL As String
Dim qdf As DAO.QueryDef

    strSQL = "Select * From tblA Where TextField='" _
    & Me.txtText & "'" 
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If

 

[dpimental]

And you could do that with each of the queries involved, before you opened the main report with the subreports?

David Pimental
(US, Oh)

 

[Remou]

You could indeed. Furthermore, you could do more exciting things along the way, if required.