Report based on query

C

crossb

Hi all,

How do I create a report that is based on a query that will not always
have the same fields? The user picks certain fields from a list then I
create the SQL through code and save that to a query. So based on that
query I would like to create a report. How does one do this since
fields may or may not be there?
I'm using Access 2000.
 
A

Allen Browne

You cannot create new controls on a report at runtime.

Some options:

1. Place controls on the report for all the possible controls. Write some
code in the Open event of the report, hide the ones you don't want (set
their Visible property), and set the Width and Left of the other controls to
space them correctly on the report.

2. Save the report as unbound, and in its Open event write a SQL statement
that aliases the fields to match the control names on the report, e.g.:
SELECT Orders.OrderID As Field1, Orders.OrderDate As Field2, ...
Assign this string to the report's RecordSource. IMHO, this is inferior to
#1, as you could run into issues with data types.

3. Programmatically open the report in design view, and set thing up. This
is the worst solution, because it prevents you from generating an MDE.
 
Top