Kelvin,
So the query looks like:????
SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = myparm()
UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = myparm()
UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = myparm()
I wirte a simple function like you suggest and, the Public variable
gets set PRIOR to the invocation of the main report?????????????
Have I got all of this right???
Bill
Kelvin said:
There are a few options to changing a query. The easy way is to have
a
text
box on the form that is calling the report. Then in the query you just
specify the criteria to equal Forms!NameOfForm!NameOfControl. This will
filter the query to your criteria, then when the report is opened, it will
already be filtered. The next method is to use a global variable liek you
currently have. However, queries cannot access global variable so you will
need to create a function to return the variable. Assuming your global
variable is strGlobalVariable
Public Function myParam() as String
myParam = strGlobalVariable
End Function
Then for the criteria in the query set to myParam(). Then depending
on
what
you are doing, just change the value of strGlobalVariable. By placing the
criteria in the query instead of as a filter to the report, you would not
need to put any code in the report. When you call the report, it will
automatically call the query, which will get the criteria you specify before
it creates the recordset that will be the data for the report and subreport.
No filters, no mess.
Kelvin
Kelvin,
I think it would be great if I could simply change the
query. Can one do something like a "change all" in
the query? Referencing my post of a few minutes
ago, like: "change DOEvalue to " & value & " ALL"
If that or something like it is possible, I could invoke
it in the OnOpen code of the main, or is that too late
in the process?
It wouldn't break my heart to eliminate the general
"bridge" module and the Public variable used in the
current scheme... make for convoluted code that's
not intuitively obvious.
Bill
Glad things are working. You can minimize the report after you
open
it.
Remember, this will minimize future windows too so put the restore
command
after the main report opens.
DoCmd.OpenReport "FundsRecentSundaySubRpt", acViewDesign
DoCmd.Minimize
DoCmd.OpenReport "FundsRecentSunday", acViewPreview
DoCmd.Restore
To answer you other question, how are the main and sub-reports linked.
If
the sub-report is linked using the field that is the criteria, then
filtering the main report should limit what is shown for the sub-report.
Is
the record source for the reports, a table or a query. If it is a query
it
would probably have been easier to set the filter in the query instead
of
the report.
Kelvin
Kelvin,
I wrote a module that queries the user for the criteria that
is to be used in setting the filter for both the main and sub-
reports. I open the sub-report and set its filter and pass
the filter value to the OnOpen of the main report via a
Public variable. Below is an excerpt from that module.
Everything works okay except that the sub-report "Design
View" flashes before the user as the filter is being set.
Is there some way to suppress that?
DoCmd.SetWarnings False
DoCmd.OpenReport "FundsRecentSundaySubRpt", acViewDesign
Reports!FundsRecentSundaySubRpt.Report.Filter = "DOE = " & FltrDonSum
DoCmd.Close
DoCmd.SetWarnings True
DoCmd.OpenReport "FundsRecentSunday", acViewPreview
End Sub
Bill
What I find easier is that when I need to have separate groups is
that
instead of using 2 reports like you have it to actually have
3.
The
main
which is just a dummy that will have a record source of only the
criteria
field. Then my 2 other reports are subreports to this main, linked
by
the
criteria field. Then setting the criteria for the main will carry
through
to the subs.
Kelvin
Kelvin,
Both the main and subreports use the same RecordSource.
The filter is not determined until run time. The reason I have
to use a sub-report is because the main groups one way and
the sub-report groups in an entirely different way, which IS NOT
a sub-group.
I tried to solve this problem by using REALLY simple functions
to accumulate summations, but was never able to find an event
in the main where I had access to the descrete records being
read and formatted by the main. If I could, I'd just collect a
running sum, much different than the main, in a Public variable
and put the results into a text-box in the report footer. It's
been
several days, but I think I added invisible controls in the main
reports detail section to invoke the accumulation function, but
the values available at the point were already the group totals
reported in the main... I need to get record access before the
main does its grouping.
That help?
Bill
I would create a new switchboard option to run this code. If
you
are
calling from a switchboard I am ssuming that it is calling the
same
report
everytime, so why not just specify the record source and filters
in
the
reports themselves? Are you passing parameters through the
switchbord
form?
Also, why are you using a report/subreport if they are both
based
on
the
same source? Understanding these things might find a better
solution.
Kelvin
Kelvin,
The main report is launched from the Switchboard manager.
If the sub-report isn't open when the OnOpen event of the
main report runs, I can put the code there. Otherwise, I'll
have to put a general module between the main report and
the Switchboard... what do you suggest?
Bill
The same would go for any properties of the subreport.
Kelvin
Kelvin,
Given that the RecordSource issue is resolved, do the
same restrictions apply to the setting of the
filter?
With
the RecordSource set, I tried to set the filter:
Me.Filter = "DOE = " & FltrDonSum
where FltrDonSum is a global variable set by the main
report, but I get 2101 error on that statement. It would
seem un-reasonable to me to not be able to set a filter.
The main report's open code sets the filter without any
problem, it's only in the sub-report that the 2101 occurs.
Bill
OK. I was able to recreate the problem, however I
get
a
different
error
message number. For some reason Access won't
