Prev: Expression Builder
Next: Crosstab query - I need aditional information
From: HWhite on 30 Jun 2008 03:27
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.

From: HWhite on 30 Jun 2008 03:30
Access 2003, by the way

"HWhite" wrote:

> 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.
>
From: Allen Browne on 30 Jun 2008 04:17
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"HWhite" <HWhite(a)discussions.microsoft.com> wrote in message
news:AF7C9B96-E430-4F46-8BE7-0E5808AC9FC6(a)microsoft.com...
> 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.
>

From: John Spencer on 30 Jun 2008 07:36
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%20Query%20By%20Form'


***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 wrote:
> 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.
>
From: HWhite on 30 Jun 2008 08:03
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!!!


"Allen Browne" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "HWhite" <HWhite(a)discussions.microsoft.com> wrote in message
> news:AF7C9B96-E430-4F46-8BE7-0E5808AC9FC6(a)microsoft.com...
> > 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.
> >
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Expression Builder
Next: Crosstab query - I need aditional information