Custom Reports

[ekta22]

Hi:

I want to know if it is possible to create a custom report where user selects the fields from the tables. I have one main table and tables linked to the many side of this table. Users want that they should be able select any fields from these tables and create their own reports. I don't see how can I do that. The only way of doing this would be to use the report wizard but I don't want that. Is there any other user-friendly way of doing this?

Thanks,

Ekta

 

[tcace]

Setup a query that combines all of the data from various tables into one spot.

Mask each field with a "User Friendly" name.

Create a form with a couple of list boxes. Make the 1st list box your "Select Field" list and set it's row source to the query and it Rowsource Type to Field List.

Have the "On Click" event of this list box add the selected value to the 2nd list box.

This is the basic setup of the "User Friendly" FE to let the user select the fields.

From here, you have several options, depending on your level of desire, time and savy. The easiest is to open a recordset of the query and dump the data to excel using a loop.

More sophisticated would be to create the report through code (which is what the wizard is doing).

In my case, I actually use this setup with 3 list boxes:
Source Fields, Sort By and Filter By. The user sends fields to the sort by and filter by boxes to define the format of the data once exported. There are buttons on the Sort and Filter list boxes that allow you to move the items up and down in the list. There is a small tab page next to the filter box that allows the user to enter criteria appropiate to the data type of the selected field (Date/Time, Yes/No, Text, ect.).

When they are ready, the whole data set exports to Excel, with the Sort and Filter defined by the user.

Good luck and have fun!

 

[ekta22]

Thanks tcase I will try it out and let you know. One more question. Will it be possible for you to send me some kind of sample database to look at?

ekta_doon@yahoo.com

Thanks,

Ekta

 

[dhookom]

There is a complete query by form "applet" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This mdb contains several forms and tables that can be imported into any application and provide users with lots of great functionality with no additional coding required.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.