using combo boxes from form to select query criteria in reports

[Amanda]

I am adding functionality to an animal management database for a wildcat
sanctuary, and creating a form so the Curator can create certain animal
reports.

The animal management relies on 3 tables: Animal Species (one record per
species), Animal Records (one record per animal, w/ Species ID), and Animal
Journal (record per note about animal, w Animal ID).

I am creating a Reports Form that set various criteria in the form: date
range, animal name, species name, journal entry type (medical entry, diet
entry, etc.). The criteria on the form will also have options for "Any"
and/or "All" (so reports can be run for "any" date, "all animals", etc.)
Then the reports are created by clicking on a button.

I have figured out how to get the query to base criteria on the form, but
still need to understand the following:

1) I can create a combo box that has options based on a query (ex. lists
each animal), but how do I add the "all Animals" option at the top of the
list?

2) if the user chooses "All Animals" how does the query know that there will
be no criteria for animals?

Thanks, Amanda Byrne

 

[Allen Browne]

Amanda, take a look at this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

It shows how to create a filter string dynamically, from only the boxes
where the user actually chose something. The example is applied to a form,
but you can apply the filter to a report just as easily, e.g.:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Although it requires you to build a bit of code to create the filter string,
it is worth the effort. You will use this technique again and again for
forms and reports. It is much easier than trying to make the query read the
"All Animals" type of thing, and much more efficient to execute.

 

[Amanda]

Thanks, I'll give that a try - Amanda

Amanda, take a look at this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

It shows how to create a filter string dynamically, from only the boxes
where the user actually chose something. The example is applied to a form,
but you can apply the filter to a report just as easily, e.g.:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Although it requires you to build a bit of code to create the filter string,
it is worth the effort. You will use this technique again and again for
forms and reports. It is much easier than trying to make the query read the
"All Animals" type of thing, and much more efficient to execute.

 

[Amanda]

Thank you very much, struggled a bit with the syntax, but it is working
nicely now.

And now I have a new problem.

I need to print the criteria used on the report, so that when someone is
viewing the report later they know what the criteria were.

How to do this?

Thanks, Amanda

 

[Allen Browne]

Great: you have now build the filter string, and you have the report
working. It is possible to print that on the report, with a text box that
has Control Source of:
=IIf([Report].[FilterOn], [Report].[Filter], Null)

There are 2 problems with that approach:
a) The filter reads like a SQL clause instead of something humanly readable,
and
b) Access does not maintain the report's FilterOn property reliably (at
least before A2007 it didn't.)

So what I prefer to do is to build up a 2nd string at the same time you
build the filter: a description of the filter in English. This kind of
thing:
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "(City = """ & Me.txtFilterCity & """) AND "
strDescrip = strDescrip & "City of " & Me.txtFilterCity & ". "
End If

You can then pass this to the report in OpenArgs (if you use Access 2002 or
later), and show it as:
=[Report].[OpenArgs]
or use a public string variable and code in the report header's Format
event.