User customization of reports w/o accessing the query 1

[Chris1977]

I have a DB built where the users can simply fill out what criteria they wish to report off of in a form then click the command button associated with that particular query. For example they want an employee attrition list for the period of Jan 1st 2003 to Jan 25th 2003. Now they just fill in the dates on the form click the attrition command button and it executes the query which i designed. Now if that particular user wanted to customize that report how would they go about doing it without giving them access to the query. I am sure some sort of pop up form bound to the report would work if it performed a filter function, but i'm unsure of how to set it up. In short all i want is if the user wants the column of "LastName" not to be shown in the report how would they accomplish that from a form? Any help you folks could give would be greatly appreciated.

Thanks
Chris

 

[KenReay]

Hi

It depends how sophisticated you want to get, but the example you gave could be failrly simply implemented, a couple of ideas:

On your form, have a series of check boxes, corresponding to (and labelled with) the names of columns on your report, by default they are all checked and user gets report with all columns, if user unchecks one of more, code in the onformat event of the (detail) section of the report, test the value of the check boxes, and sets the visible property of the checked column(s) to false.

A similar result could be achieved by having two list boxes on the form, the leftmost conatins a list of available columns, the rightmost a list of selected columns, by default all columns are listed in the Selected column, the user may move columns between the two lists (this is a common windows technique which users would recognise), again code in the onformat event of the (detail) section of the report tests the selected items in the lests and sets the .visible property of the corresponding columns.

Does that help? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oharab]

I have used a system where instead of just using check boxes, I had a set of check boxes defining tables, which then fed the fields from each table into a list box. The user would then choose which combination of fields to put on the report. When to print button was pressed the code built the query from scratch, using the fields chosen by the user.

Just my 2p's worth!

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[Chris1977]

First off your ideas worked great so thanks for the help i appreciate it.

One final problem. The field did disappear off of the report but is there an easy way to have the report reformat so if a middle column is removed you don't see a large hole in the middle of the report itself. If this is not possible great but if it is in some way do able i'd like to know how.

Thanks folks,
Chris