Using Filter by Form to create a Query and then a Report

[HWhite]

Somebody has told me that they would like to generate their own reports in
Access. This person does not know access at all, so I need to come up with a
way to make this as user friendly and efficient as possible.

I'm working off one master table that uses one lookup table. This basically
is a list of documents received, who they were from, what the date was, what
it was about, document titles, legal issues, etc.... Legal stuff.

Users are entering data into the database via an input form.

My thought was to use this same form and utilize the Filter By Form feature.

Here's what I've done and where I'm stuck:

I created a read-only replica of the input form so no accidents would happen
if they started typing into the form by accident.

I created a button that puts the form into Filter By Form mode. This,
unfortunately, makes the other command options on the page unavailable.

I've tested several filtering criteria and it works fine.

I want the person to then view the results in datasheet mode to make sure it
is what they want. I've can do this.

Here's where I'm stuck.

If the data is what they want, they'll want a report from it. It is enough
data that the datasheet view will not print well.

I want them to click a button that saves the filter as a query (query name
forced), and then prints a previously created report that is based on that
query. It should prompt them for three things... 1) The report name, which
will be placed in the report header automatically (need help with that). 2)
Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is
necessary because the data would need to be sorted by at least the date, and
probably one other criteria and then the date as a secondary sort. Ideally,
they would choose these sorting options from a drop down box.

I'm frustrated because the "save to query" option disappears when I apply
the filter... which means it would need to be saved before the filter is
applied. Backwards, but I can live with it as long as I can figure out how
to force the query name behind the scenes. That, plus creating the prompts
would really, really help me out.

Thanks for anybody willing to help me with this.

 

[HWhite]

Access 2003, by the way

 

[Allen Browne]

You can apply the form's filter as the report's WhereCondition when you open
it.

The event procedure code for the button on your form would look like this:
Dim strWhere as String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

There may be some more work to do if the form is filtered with combos that
have the first column hidden.

An alternative approach is to put some search boxes directly in the form
itself, where the user can find them. You can easily set these up to avoid
the problems with the combos. There is a bit of work in learning this
approach, but it is invaluable for both search forms and flexible report
filtering. It it sounds interesting, download this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[John Spencer]

Customized report/query based on user input

You might want to consider the Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided by
DH QBF can replace many "canned" reports. The developer imports several forms,
tables, a query, and a report from the DH_QBF.mdb, creates some master
queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a
main form. The main form has options to send/export the records to print, Word
table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats
allow he user to automatically open the target application. The Word merge
process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[HWhite]

Thank you Allen. That code worked perfectly.

So, that gets me through the part about filtering the data and then
generating the report based on that filter, but I still need some assistance
with prompting the user to provide up to 2 sorting criteria, and a prompt for
the report name that will be placed in the report header. I tried a few
things and they didn't work.

I wish I knew code writing...

Thanks again for your assistance!!!

 

[HWhite]

Thanks John. Looks very interesting! I'll have to play with it.

 

[Allen Browne]

To show the report name in the header, just add a text box with this
ControlSource:
=[Report].[Name]

For dynamic sorting or reports, set the ControlSoruce of the GroupLevel in
Report_Open. Details in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html