Monday, May 07, 2001 23:07:10
Hello. I know how to apply a form's FILTER to a report when you open it, but
I am curious if it possible to apply a form's RECORDSOURCE to a report at
run-time.
What I have is a form named "browse_all_form", a continuous form which
normally shows all the records via a query named "main_query," but it's also
used in conjunction with a "search_form" which opens up the "search_form" and
changes its "record source." The record source is defined in Visual Basic
using the "SQL view" from the original queries (the queries were, in essence,
filters saved as queries). I would like the report to be able to inherit the
RECORD SOURCE from the form.
Inheriting the filter won't work, because I seem to be able to only filter one
criteria at a time; utilizing RECORDSOURCE allows me to sort and filter on
any number of different criteria, and I want that same criteria to be applied
to a report.
Any tips?
Larry
From Steve Jorgensen
A report's recordsource can be set at one of 2 times:
1. During design.
2. During execution of the report's Open event handler.
If the repeort should always get its RecordSource from a specific
form, you can do this very simply. If you need to somehow tell the
report what to do, it's more complex because there is no direct way to
pass an argument to a report. In this case, you must store data in a
global variable that the report's code can then examine to determine
what it should do.
From Larry Linson
A "dead easy" way to accomplish this is to modify the SQL in the form's
code, and store it back into the SQL property of a Query that you use as the
RecordSource of the Report. The Query won't be "preoptimized" by Jet, but it
pays to "recompile" Queries from time to time as the optimum execution plan
changes as data is added to the tables.
From Steve Jorgensen
I avoid modifying stored objects in the front-end like the plague. I
have gone so far as to create temporary databases to hold such things
and delete it when the session ends.
But that's just me.
On Sat, 05 May 2001 01:07:34 GMT, "Larry Linson"
wrote:
[A "dead easy" way to accomplish this is to modify the SQL in the form's
[code, and store it back into the SQL property of a Query that you use as the
[RecordSource of the Report. The Query won't be "preoptimized" by Jet, but it
[pays to "recompile" Queries from time to time as the optimum execution plan
[changes as data is added to the tables.
[
larry.linson@ntpcug.org (Larry Linson) wrote in
[qnII6.2450$sL4.434064@paloalto-snr1.gtei.net]:
A "dead easy" way to accomplish this is to modify the SQL in the
form's code, and store it back into the SQL property of a Query
that you use as the RecordSource of the Report. The Query won't be
"preoptimized" by Jet, but it pays to "recompile" Queries from
time to time as the optimum execution plan changes as data is
added to the tables.
This will cause bloat in the front end because you will be creating
and overwriting existing QueryDefs. I try to avoid this in order to
never have to regularly compact the front end.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
larrytucaz@mindspring.com (Larry R Harrison Jr) wrote in
[9d0636$jao$1@nntp9.atl.mindspring.net]:
[That is exactly what I want the report to do--inherit its values
[(recordsource, NOT filter) from the form named "browse_all_form."
[So, if it's simple, how do I do it?
In the OnOpen event of the form:
If IsLoaded("frmYourForm") then
Me.RecordSource = Forms!frmYourForms.RecordSource
End If
If the form isn't loaded, it will use the report's saved
RecordSource.
The IsLoaded() function can be found any number of places, at
Microsoft, in the Solutions sample database and surely on the
Access Web.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
On Fri, 04 May 2001 23:37:36 GMT, nospam@nospam.nospam (Steve
Jorgensen) wrote:
[snip]
In this case, you must store data in a
global variable that the report's code can then examine to determine
what it should do.
You have to leave the data where the report can find it. A public
variable is one way to do that.
You could also use a table, a registry entry, an ini file entry, an
argument class (probably--haven't tried it), or let the report read
the properties from the form. I'm sure there must be other ways as
well.
--
Mike Sherrill. MSherrill@compuserve.com
Information Management Systems
On Mon, 07 May 2001 21:22:11 GMT, MSherrill@compuserve.com wrote:
[On Fri, 04 May 2001 23:37:36 GMT, nospam@nospam.nospam (Steve
[Jorgensen) wrote:
[
[[snip]
[[In this case, you must store data in a
[[global variable that the report's code can then examine to determine
[[what it should do.
[
[You have to leave the data where the report can find it. A public
[variable is one way to do that.
[
[You could also use a table, a registry entry, an ini file entry, an
[argument class (probably--haven't tried it), or let the report read
[the properties from the form. I'm sure there must be other ways as
[well.
If the report will aways be opened through a specific form that is
opened using DoCmd.OpenForm, and not using New Form_[formname, the
form propery method will work - otherwise not. The other methods
mentioned require unnecessary data persistence or complex code.
Basically, I always use the public variable approach because the code
is simple, and I won't have to rewrite it if i ever want to open the
report from a different form or allow multiple instances of the form
that opens the report.
Steve Jorgensen
