SQL Generated Report ?

[Sseleman]

I have created a form with a series of radio buttons which ultimately generate a SQL statement in a text box during runtime.

I want to be able to create a report which has this SQL string as its source.

I am thinking that I need to save the SQL string as a query and create a new report during runtime, but I cannot figure out how to do this.

I know I could use the values from form controls in a pre-existing Access based query, but I would prefer the SQL string if possible.

System: Access 2002 / ADO.

Any help wil lbe greatly appreciated.

Sseleman.

 

[RickSpr]

Are you talking about creating a report from scratch, in effect writing your own Report wizard?

If not, you need to give us some design limitations. Are the report's layout and field inventory predetermined? What parts of the SQL statement, besides the WHERE clause, are variable? Does the report involve grouping, or is that variable, too?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[KenReay]

Hi

Why not:

Create a query containing the fields you can Select, but no Criteria.

Create a Report based on the above query

In your radio buttons etc create the SQL, and save it in a text box on the form (visible = false, txtSQL)

Have a button on the form to open the report mentioned above

in the on open event of the Report set the recordsource to txtSQL string (eg Me.Recordsource = Forms!MyForm!txtSQL)

Regards

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

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

UK

 

[Sseleman]

Thank you to you both for replying ~ I have actually achieved the first stage of my problem in a similar way to Rick's suggestion ~ and I have hit another issue which could be where Ken is coming from.

The report is sourced from the data in 1 table.

The Form with radio buttons is to enable a user to define the output of their own report without needing a command of SQL.

In its basic form it will be a SELECT * FROM - and if required WHERE, GROUP BY, and ORDER BY statements to be built.

This suggests that the report layout needs to be dynamically designed ~ and this is my brick wall for the moment.

Currently, I have created a SELECT ALL query called qryTest, and I have a rptTest designed using the wizard pointing to it. When the report is run at this point, I get a columnar report with every field which is as you would expect.

During runtime I update the qryTest to equal the built SQL string.

However, this only works on the SELECT ALL, if I determine certain SELECT FIELDS in my statement (ie SELECT column1, column3...) without any WHERE clauses, I am then prompted for the missing fields (ie Enter value for Column2)that would normally be included in a SELECT ALL, immediately after the OpenReport method is called. Only the data returned by the SQL statement is populated, so I am half way there.

I am assuming I get these prompts because rptTest has text boxes which are bound to the field in the original SELECT ALL query

I wish the report to show only the columns in the Select statement, and stop prompting for values for columns that are NOT to be displayed.

Sseleman.

 

[Sseleman]

Thankyou for time that you guys have spent helping me ~ the problem has been solved.

The solution is quite long - but exactly what I required.

I found it on page 665 in Access 2002 Desktop Developers Handbook.

This thread can now be closed.

 

[dhookom]

If you want a nice solution for that is ready to drop into any mdb, check out the DH Query By Form at

http://www.rogersaccesslibrary.com/OtherLibraries.asp.

You can add the forms and two tables from this applet into your mdb and have a custom reporting tool within an hour.

The user can create custom queries and click a couple buttons to send the results to Print, Excel, Word, CSV, Html, Word Merge,...

Duane
MS Access MVP

 

[Sseleman]

Duane,

Excellent!

Very impressed, not only is it a more elegant solution, it also provides the extra functionality I was looking for.

Many thanks.

Sseleman