How to get a query by form to output to a report 1

[Mindy343]

I've been stuck on this one for awhile and still can't find the answer...(I'm not skilled enough to figure it out myself!). I want to create Access reports from the queries generated by the "applet" mentioned in Thread 703-663045,{

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH

Query By Form'}. He explained the dilema perfectly but got no response so I hope I have better luck.
I can print the query and output it to Word but I'm just not experienced enough to figure out how to create a report that can change based on the user input.

 

[dhookom]

The Query By Form applet builds a sql statement and then applies this SQL to a saved query. I expect you could use the SQL statement and set the RecordSource property of your report to the SQL statement in the On Open event of the report.

I'm not sure how you would choose the report and make sure you had selected all of the appropriate fields.

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]

 

[Mindy343]

That's exactly the dilema ...How do I create a report that will show the fields requested by the user and then direct access to use the sql statment created. Is there another way to create "Reports on the Fly"?

 

[dhookom]

Creating a report on the fly is difficult. I don't know of any Access MVPs that do this. What is your specific requirement? What is wrong with sending the data to Excel?

Do you need to just create a bunch of standard, canned reports and then allow users great flexibility in filtering them?

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]

 

[Mindy343]

I've already created about 30 reports and each time I create one someone askes for the same info only sorted differently, without some fields etc, add some fields, only data for one site, etc... Thus the interest. Maybe outputting to exel is the best idea in this case...Thanks bunches for your answers.

 

[dhookom]

Filtering an existing report is standard for most reports. It is generally fairly easy to create a form with text and combo boxes that allow users to select criteria for a report.

Adding the ability to display or hide fields/controls is a little trickier but not too difficult. The same is true of changing the sorting and grouping.

Consider asking your "customers" to complete a fairly long change request form that asks for justification, specifications (label captions, field values, calculation formulas, layout, colors, font sizes and names,...), ROI (return on investment), testing procedures, data verification, signatures of approvers, ... Your requests may drop significantly ;-)

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]