Report building at run time

[ismailr]

Hi,

I have a query based on multiple tables which extract different fields from tables based on parameters. I am using this query as source for report.

The query result will have different number of fields so there is no fix template for report. On run time VBA should first clear all existing report text fields and labels than dynamically should create the report text and label. Then present the data from the query in the report.
Is it possible? Please guide me.

Thanking you in anticipation.
Ismail

 

[Ranman256]

Just like a Crosstab query, you can't get a fixed field count, but I do know the max#.
I build a 'report' table with ALL the possible fields, then my query appends to the fields that exist.
Then report on the report table.

 

[ismailr]

The cross tab query does not work because of different data type.
I wrote first query to join two tables and extract data. Then script crate table and modify the table structure to include additional fields. Then script transform query output and insert into report table created above. Thiis report table does not have fixed number of columns. So how I can have these field on report?

Even if I consider max possibility of columns how i can put the column headings. How I can disable if there is no data.
Any idea?

Thanks
Ismail

 

[gemma-the-husky]

Difficult to do.

It will be a lot easier just to export the data to a spreadsheet and let users manipuLate the spreadsheet.

There are limits to what it is practical to do.

It's somewhat easier to have a form/report with many available controls, and hide the ones you don't want.

But placing new controls on a form at run time is going to be tricky.