Report Macros

Would it be possible to make a form that contains a list of reports then
when a user clicks on one of them that corresponding list or combo boxes
would appear to the righ for them to fill in parameters for that report.

I have created 3 tables
ReportList (ReportName) (Description) (Category) (SubCategory) etc.
ReportObjects (ObjectName) (ObjectLabel) (Description) (Source) etc.
ReportObjectUsage (ReportName) (ObjectName)

I created Main Form that has the report List
and another that contains all the objects that will be needed
Its like one big form of parameters


My Imagination is bigger than my abilities..........

What you are suggesting is actually a very good design for end users. Giving
them lots of flexibility for filtering reports makes for a great program.

But it does mean a considerable amount of code. You may even find that this
form contains more code than any other in your database, so you will need
some experience with VBA to achieve it.

The basic strategy is:
a) Use the AfterUpdate event of the control where user chooses a report so
show/hide the filter boxes as appropriate.

b) In the Click event of the button that opens the reports, visit each of
the filter boxes. If ther are Visible and Enable and not IsNull(), then
include them in the filter string you build up to use as the WhereCondition
for OpenReport. (You probably want to build a description of the filter in
English at the same time, so you can print that on the report - it's
meaningless without it.)

Personally, I don't try to put all these things into a table, but just do it
in the code.

In Northwind, there's a form called [Sales Reports] that offers 3 reports,
and enables a list box for one of them. It demonstrates the basic approach
to (a) above.

The approach of building up the WhereCondition string is identical to
building the filter string for a search form. You can download an example
and pull it apart he
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

When it comes to passing the description string to the report, in Access
2002 or later you can pass it in OpenArgs. In earlier versions, you need to
set a public string variable just before you OpenReport, and then read (and
reset) the string in Report_Open. What I personally do is create a little
wrapper function named OpenTheReport() that handles this, defaults to
acViewPreview, accepts the description, doesn't get messed up by the
unserviceable Filter argument, correctly filters the report even if it is
already open, handles error 2501 silently, and returns True if the report
opened or False if it was cancelled. It's very simple, but if it's any use
you can copy it from he
http://allenbrowne.com/AppPrintMgtCo...#OpenTheReport
(You can remove the block for setting up the printer for the report.)

HTH

--
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.

"PHuser" wrote in message
...
Would it be possible to make a form that contains a list of reports then
when a user clicks on one of them that corresponding list or combo boxes
would appear to the righ for them to fill in parameters for that report.

I have created 3 tables
ReportList (ReportName) (Description) (Category) (SubCategory) etc.
ReportObjects (ObjectName) (ObjectLabel) (Description) (Source) etc.
ReportObjectUsage (ReportName) (ObjectName)

I created Main Form that has the report List
and another that contains all the objects that will be needed
Its like one big form of parameters


My Imagination is bigger than my abilities..........

Thanks so much for the reply Allen, it is a sweet start for me. I have a
feeling this will be quite an adventure and look forward to conquering it.
"Allen Browne" wrote in message
...
What you are suggesting is actually a very good design for end users.
Giving
them lots of flexibility for filtering reports makes for a great program.

But it does mean a considerable amount of code. You may even find that
this
form contains more code than any other in your database, so you will need
some experience with VBA to achieve it.

The basic strategy is:
a) Use the AfterUpdate event of the control where user chooses a report so
show/hide the filter boxes as appropriate.

b) In the Click event of the button that opens the reports, visit each of
the filter boxes. If ther are Visible and Enable and not IsNull(), then
include them in the filter string you build up to use as the
WhereCondition
for OpenReport. (You probably want to build a description of the filter in
English at the same time, so you can print that on the report - it's
meaningless without it.)

Personally, I don't try to put all these things into a table, but just do
it
in the code.

In Northwind, there's a form called [Sales Reports] that offers 3 reports,
and enables a list box for one of them. It demonstrates the basic approach
to (a) above.

The approach of building up the WhereCondition string is identical to
building the filter string for a search form. You can download an example
and pull it apart he
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

When it comes to passing the description string to the report, in Access
2002 or later you can pass it in OpenArgs. In earlier versions, you need
to
set a public string variable just before you OpenReport, and then read
(and
reset) the string in Report_Open. What I personally do is create a little
wrapper function named OpenTheReport() that handles this, defaults to
acViewPreview, accepts the description, doesn't get messed up by the
unserviceable Filter argument, correctly filters the report even if it is
already open, handles error 2501 silently, and returns True if the report
opened or False if it was cancelled. It's very simple, but if it's any use
you can copy it from he
http://allenbrowne.com/AppPrintMgtCo...#OpenTheReport
(You can remove the block for setting up the printer for the report.)

HTH

--
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.

"PHuser" wrote in message
...
Would it be possible to make a form that contains a list of reports then
when a user clicks on one of them that corresponding list or combo boxes
would appear to the righ for them to fill in parameters for that report.

I have created 3 tables
ReportList (ReportName) (Description) (Category) (SubCategory) etc.
ReportObjects (ObjectName) (ObjectLabel) (Description) (Source) etc.
ReportObjectUsage (ReportName) (ObjectName)

I created Main Form that has the report List
and another that contains all the objects that will be needed
Its like one big form of parameters


My Imagination is bigger than my abilities..........