Dynamic Created Reports 1

[EdwardSalas]

Morning all,

I currently have a form that allows for the user to choose from fields they would like to see in the query/report. Essentially, I have a list box of metrics (i.e. sales $, units, etc) that the user chooses from so they can customize what they would like to see. I am passing this information into a query (and excel) via a querydef. However, my question is can I create a report that is just as dynamic? In other words, can I create one report that can be flexible enough to allow for user defined fields?

Many thanks to all on this forum...I've learned a tremendous amound from all of you...

Eddy

 

[THWatson]

Eddy
As an example, let's assume that your form is called frmChooseReportDetails, and in that form the user selects from two fields (Sales and Units).

One approach would be this:
In the query that populates the report, in the Sales column put the following as criteria...
Forms!frmChooseReportDetails!Sales

In query's Units column put as criteria...
Forms!frmChooseReportDetails!Units

Then on the frmChooseReportsDetails, put a command button to run the report.

Tom

 

[EdwardSalas]

Tom,

Thanks for responding...that would work if my query was a "static query". The query is actually being created on the fly with my querydef. I have the user chosing from a list box which fields they want to view in the query. So one query may just be sales $, while that same query for another user could be sales $, Units, and Gross $. I'm looping through the list box and creating my sql select statement from this. That statement I then pass to the query via querydef...

Eddy

 

[dhookom]

There is a complete query by form "applet" at

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

You simply need to import some objects into your existing application and then create some "master" datasource queries.

Users can select fields from your master queries and set sorting, filtering, grouping,... Their "designs" are saved in two tables for easy changing and updating. The results are displayed in a datasheet subform. The main form containing the subform has buttons to send the results to printer, html, csv, Word Merge, Word Table, Excel, Graph,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PWise]

dont put any control source in the reports text boxes and in the report open loop truh the querydef fields and set the control source

 

[NewfieSarah]

Hey all, this is just what I have been researching and i can't seem to get the right information for this process. Just like Eddy I dont have anything created yet, but I would like to give my users the ability to create reports on the fly based on my different filtered forms. this way the form is not generic with all the data in my table, it is the filtered data with the specific fields that they require. I have been told that dynamic reports are very hard and difficult, and not to go that root, but I like the idea of the list box, whatever is selected in the list box is the data seen on the report. So How is this possible, I need help I am a newbie. Thanks

 

[dhookom]

Did you download the sample applet that I suggested? This solution gives your users the ability to create "reports/queries" dynamically.

When I include this same applet in custom applications for clients, I normally charge $500 (for the minimal setup). I give this away free on the web site.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NewfieSarah]

Well i breifly looked at it, though it seems very confusing. Like I only want to do something so simple and it yet is so difficult.

 

[dhookom]

You only need to:
[li]import the listed objects into your MDB[/li]
[li]create some queries that combine tables and change field names as necessary[/li]
[li]Delete the sample records from the two tables[/li]
[li]You are done[/li]

When I create tables and fields, my object names are not meant for user consumption. I also don't expect them to understand relationships/joins between tables. Hopefully, you as the developer understand this and can create "data source" queries that join and simplify your tables.

Most of my users will click two buttons to view the results of the query/report in Excel. From Excel they can do what ever they want with formatting the output to a printer or screen.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NewfieSarah]

so in other words you are saying that I can't do it the way my people would like it to be done?? By having them create there own reports with the filtered data on there forms? They would have to use excel in your example Correct?? and have a generic report and format it themseleves. okay well I will have another look at that sample as well. Thanks

 

[dhookom]

You don't have to push the data to Excel as you can print it from the applet.

You could attempt to recreate the report wizard and use CreateReport and CreateReportControl methods. You would need to determine layout on the fly as well as set data source, filtering, sorting, page sizes,... I don't know any Access MVPs who have attempted to code this solution.

The query by form applet allows a user to select fields, sorting, filtering, output destination, grouping,... with fairly decent flexibility and performance. You can have it implemented in a matter of minutes without touching any code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NewfieSarah]

Okay, so Tell me more about this query by form applet situation?? It sounds like we are on that same page more or less. So the data can be pushed int excel or it can be printed form the applet?? Though you wouldnt recommend creating my own report wizard since no one that you know would have tryed it before. Thanks for the help!

 

[dhookom]

What more do you want to know about the Query-By-Form applet? It's free for download and can be used in any "non-commercial" application. All you need to do is unzip and run it to see how it works. If you have questions, come on back.

Regarding creating a complete report writer on your own isn't anything I have seen. That's why people use the report designer in Access. Try allow the report wizard auto create a report for you and see if you think it is formatted correctly without modifications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]