View criteria used to filter a report

[HAZBONES]

Basics:
I created a report (rpt_Area) based upon a form with 10 combo boxes. I want
all of the criteria selected on the form showing in the footer of the report
in unbound text fields 1~10.

Background:
The user can use any combination of fields on the form to create the
criteria for the report. When the user has selected all of his/her criteria,
they can then hit a "Print Report" button that will open the report
(rpt_Area) based upon the selected criteria.

Needed:
The report works fine. It opens up and is filtered by the combo box criteria
I selected on the form. What I need to know is how to make the 10 unbound
text boxes I have in the report's footer section show the criteria that was
used to filter the report.

Here is the VBA I am using on the form's command button OnClick event to
open the report:

Private Sub AreaRptbtn_Click()

Dim stDocName As String
Dim strDataFilter
strDataFilter = "1=1 "

'If statements for the form combo boxes...


stDocName = "rpt_Area"
DoCmd.OpenReport stDocName, acPreview, , strDataFilter

 

[Allen Browne]

You can show the report's filter on the report with a text box that has a
ControlSource of:
=IIf([Report].[FilterOn], [Report].[Filter], Null)

There are 2 practical problems with that approach:
a) There are cases where Access (prior to 2007) does not maintain the
FilterOn property correctly, so it may not work.

b) The filter string is probably not what you need to see, e.g.: it may say
things like:
([StateID]=9)
when you really need to see something meaningful such as:
State = New York.

Therefore the better solution is to modify the code in your button's On
Click event so that it builds 2 strings:
- one for the filter
- one as a description for the end user.
You can then pass this to the report, and show the meaningful description of
the filter.

Use the OpenArgs of OpenReport to pass the description (Access 2002 or
later), or a public string variable in earlier versions.

 

[Larry Linson]

Not nearly all my Reports use a Filter to select the Records. I often use
the limiting values entered on the Form to create an SQL Statement and
replace the Report's RecordSource property with that SQL Statement in the
Open event of the Report. But, in code, you can pick up the values from the
Form, provided it is still open and you haven't changed the values in some
way, and store them into the appropriate Controls in the Report's Header or
Footer. I usually use the Report Header for this, so it will be immediately
obvious to the user when they first pick up the Report what selection
criteria have been used.

Larry Linson
Microsoft Office Access MVP