Help with Dynamic Queries/Report

T

THANX LW

HI all,
I am confused with how to tackle dynamic query/Report situation.
I Have a simple query which has some fields from tables and then some
calculate fields and a report based on this query has a criteria form to
filter records, but every time report will be run some fields will stay the
same and there might be some additonal fields needed at run time.
can somebody please suggest an efficient way to tackle this problem.
I can think of only two options......either calculate all the possilbe
fields in a query and put all the fields on report but at run time make
unnecessary fields invisible(not that efficient i think)........or just
create the query and report with VBA(difficult options....will have to learn
how to do that).....
I appreciate all the help and suggestions......Thanks....
 
D

David Lloyd

Below is a KB article that explains how to create a dynamic report where the
fields are not known ahead of time. It applies to a crosstab query, but is
applicable to other types of queries.

http://support.microsoft.com/default.aspx?scid=kb;en-us;328320

A second option is to open the report in design view and programmatically
change the Control Source properties of the textboxes using the fields from
the query behind the report. You can use the OpenReport method with the
acViewDesign parameter and the acHidden parameter to open the report in
design view but hidden and make the appropriate changes. For example:

DoCmd.OpenReport "MyReport", acViewDesign, , , acHidden
Reports("MyReport")("FirstFieldTextBox").ControlSource = "MyFirstFieldName"
DoCmd.Close acReport, "MyReport", acSaveYes

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


HI all,
I am confused with how to tackle dynamic query/Report situation.
I Have a simple query which has some fields from tables and then some
calculate fields and a report based on this query has a criteria form to
filter records, but every time report will be run some fields will stay the
same and there might be some additonal fields needed at run time.
can somebody please suggest an efficient way to tackle this problem.
I can think of only two options......either calculate all the possilbe
fields in a query and put all the fields on report but at run time make
unnecessary fields invisible(not that efficient i think)........or just
create the query and report with VBA(difficult options....will have to learn
how to do that).....
I appreciate all the help and suggestions......Thanks....
 
T

THANX LW

thanks for the suggestions. i will try these suggestions and see if it works
for my situation.
please dont mind if i come up with another question while solving this...
Thanks once again.
 
Top