Report Form Design

[apollo8359]

My report form is turning out to be the most complicated form in the
database. They want combo boxes, radio buttons, date parameters with an on
and off option and four kinds of output, preview, print, snapshot and excel.
I realize I need a case statement to handle all the options but my question
revolves around the gathering of the desired data. I want to know which
option is the best to use and gives me the most flexibility, running a select
statement with the criteria concatinated into the where clause, or the
docmd.openreport, with the where clause there? This has to be a common issue
with all database designs, as a self taught rookie, where can I find a good
example of how to get a form to dialogue with a query? I used the following
with the open report option, but it only narrowed by REPEID, not date.

RepEID=2583 and [Opened] >=07/01/2005 and [Activity] <= 07/31/2005

Thanks in advance for any help on this.

 

[Allen Browne]

The most flexible approach is to use OpenReport with a WhereCondition built
from only the boxes there the user entered criteria.

For an example of how to build that kind of string, download this:
http://allenbrowne.com/unlinked/Search2000.zip
Although the example is actually building the Filter for a form, the code is
identical to building the WhereCondition for OpenReport (since both are
actually the WHERE clause of a query.)

As the example demonstrates, you need to use # around literal date values in
your WhereCondition string, and " around Text fields. So, the target string
you are after would be:
(RepEID = 2583) and (Opened >= #07/01/2005#) and (Activity <=
#07/31/2005#)

If you create a form that offers all the reports, shows/hides the
appropriate filters for each, builds the WhereCondition, and opens the
report in the right mode, that form will probably contain more code that
most other forms in the database.

The WhereCondition for OpenReport is not suitable for actions that have no
WhereCondition, such as SendObject or TransferSpreadsheet. It is also not
suitable for reports that do not return the field you wish to filter on,
e.g. if you select a date range but group by something else. If you need to
filter a subreport to the same date range, you are probably best having the
subreport's query refer to the controls on the report form, since subreports
don't have a WhereCondition and you cannot specify a range as part of the
LinkMasterFields/LinkChildFields.

 

[Duane Hookom]

I place as much filtering in the where clause of the DoCmd.OpenReport method
as possible. My code might look like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtActivityStart) Then
strWhere = strWhere & " And [Activity]>=#" & Me.txtActivityStart & "# "
End If
If Not IsNull(Me.txtActivityEnd) Then
strWhere = strWhere & " And [Activity]<=#" & Me.txtActivityEnd & "# "
End If
'--etc--
DoCmd.OpenReport Me.lboReportName, Me.optGrpView, , strWhere

You can't use this method to filter subreports or for some totals query
where the filtering fields are not included in the report's record source.

My more sophisticated applications have report system tables for "reports",
"criteria controls", and "report/criteria controls". When a user selects a
report, some code runs to enable or disable appropriate controls based on
the report.

 

[apollo8359]

Thanks Allen, that worked and the search form gave me a few ideas. I am stuck
on Park Avenue in NYC but I wish I were in Freemantle like I was twenty years
ago. Not a bad gig you got going.
Michael

The most flexible approach is to use OpenReport with a WhereCondition built
from only the boxes there the user entered criteria.

For an example of how to build that kind of string, download this:
http://allenbrowne.com/unlinked/Search2000.zip
Although the example is actually building the Filter for a form, the code is
identical to building the WhereCondition for OpenReport (since both are
actually the WHERE clause of a query.)

As the example demonstrates, you need to use # around literal date values in
your WhereCondition string, and " around Text fields. So, the target string
you are after would be:
(RepEID = 2583) and (Opened >= #07/01/2005#) and (Activity <=
#07/31/2005#)

If you create a form that offers all the reports, shows/hides the
appropriate filters for each, builds the WhereCondition, and opens the
report in the right mode, that form will probably contain more code that
most other forms in the database.

The WhereCondition for OpenReport is not suitable for actions that have no
WhereCondition, such as SendObject or TransferSpreadsheet. It is also not
suitable for reports that do not return the field you wish to filter on,
e.g. if you select a date range but group by something else. If you need to
filter a subreport to the same date range, you are probably best having the
subreport's query refer to the controls on the report form, since subreports
don't have a WhereCondition and you cannot specify a range as part of the
LinkMasterFields/LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My report form is turning out to be the most complicated form in the
database. They want combo boxes, radio buttons, date parameters with an on
and off option and four kinds of output, preview, print, snapshot and
excel.
I realize I need a case statement to handle all the options but my
question
revolves around the gathering of the desired data. I want to know which
option is the best to use and gives me the most flexibility, running a
select
statement with the criteria concatinated into the where clause, or the
docmd.openreport, with the where clause there? This has to be a common
issue
with all database designs, as a self taught rookie, where can I find a
good
example of how to get a form to dialogue with a query? I used the
following
with the open report option, but it only narrowed by REPEID, not date.

RepEID=2583 and [Opened] >=07/01/2005 and [Activity] <= 07/31/2005

Thanks in advance for any help on this.

 

[Allen Browne]

Thanks Allen, that worked and the search form gave me a few ideas.
I am stuck on Park Avenue in NYC but I wish I were in Fremantle like I was
twenty years ago. Not a bad gig you got going.

Shh! Don't tell everyone. We don't want the whole world here!