Dynamically Generate Customized Report

[usermj]

HI there,

Recently one of my clients asked me to design a user-interface, which allows them to choose the columns listed in the final report by ticking relevant fields.

We usually bind TextBox objects with table fields in the report design view. In this case, as we can't anticipate which table fields users want to put in the final report, how can I design my report? Is there any chance I can use VBA to generate some TextBox object and bind them with the table fields chosen by users from the previous form.


Many Thanks

 

[usermj]

I just looked some previous posts. I know it may work if sets visible property of textbox to 'true' to those columns which have been chosen by users.

But I'm desirable to see whether any other solution can make the layout of report more nice and well-organized.

Thanks

 

[sportsguy]

yes there is. . .

basically, you need to create a standard report, or several standard reports, where the field names on the query and the report never change, ie: Dim1, Dim2, Dim2, Value1, Value2, Value3, etc. that is linked to a rptQuery, with those specific field names.

Then depending upon the selection choices in the boxes, you create in code the SQL query that saves the rptQuery as the query that supports the report, with Dim1 as whatever the first check box is, Dim2 as the second check box, etc. . .

I have done this on a very small scale, whereby i change the underlying query of the report to fit what the person wants, by just changing the rptQuery code before the report runs. . .

sportsguy

 

[trucktime]

Report

May be this will help:

http://www.fontstuff.com/access/acctut19.htm

 

[usermj]

Thanks guys..

yes there is. . .

basically, you need to create a standard report, or several standard reports, where the field names on the query and the report never change, ie: Dim1, Dim2, Dim2, Value1, Value2, Value3, etc. that is linked to a rptQuery, with those specific field names.

sportsguy

As I can't anticipate how many columns users will choose, how many textboxes I should create in the report ?

thanks