Autocreate Report

[chinitzj]

I have a form whose main purpose is to define a query. The form allows the user to specify criteria for up to 4 different fields in a table. The table actually contains many more fields so the form also allows the user to select which fields will appear in the query (in a multi-select listbox). For example, the query may include 10 fields from the table with specific criteria for 2 fields. When a button on the form is clicked I generate the query.

I then want the button to create a report based on the query. I essentially want to perform the AutoReport feature that is accessible from the "reports" tab on the main database window by clicking the "new" button. Is there a way of doing this using VB? I am shying away from using OpenReport on a previously created report that shows all potential fields and then hiding fields based on what is selected on the form. The amount of variation in the number of fields makes programming for this very cumbersome. It seems that there has to be a better way. Any other suggestions?

 

[dhookom]

You may want to consider importing the Query by form applet from

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

This fully functional applet allows the developer to import some objects (forms, reports, tables,...) from the sample and then create some data source queries. A data source query is a simple query that joins related tables and possibly rename/alias fields. Users will not need to understand your structure in order to use your query by form.

That's all you need to do to allow users to select a datasource, choose fields, set criteria and sorting, maybe group by and total, and then view the results. The query "definitions" are saved for future use. The results are a couple clicks away from sending to CSV, print, Excel, HTML, Merge, Word Table,...

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]

 

[chinitzj]

Thanks Duane,

I just stumbled upon your applet and downloaded it. Do you know if I can make this work in Access 97? I do have a computer with Access 2000. I was just about to try to open your applet there and see if I could extract what I needed and try to get it to work in 97.

 

[dhookom]

The applet was originally written in Access 97 (or maybe even 2.0). You should be able to open it in Access 2000 and convert it to 97 without any issues. You may need to open a module while in 97 to check the references.

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]

 

[chinitzj]

Duane,

Thanks a million. I have not implemented it yet, but this looks to be exactly what I need.