Using parameters to generate a report

[Gary Schuldt]

Someone must have done this before:

I need a Form, or a Wizard for a Form, that will support parametric
generation of a query or report.

A simple example: Here are 3 fields of selection interest in my
DB--ProductName, StoreType, and RegionName.

What I want to see are displays like "a list of all ProductNames stocked by
StoreType="Convenience" and RegionName="NW".

So I'd like the Selection Form to show a drop-down list for all values of
each of those 3 fields, and associated with each dropdown would be another
control showing the value the user clicked on.

After they'd made their selections, there'd be a Run button to run the query
/ report.

OTOH, maybe this is so simple that there's no need for a template or wizard
to walk me through this! Just tell me, I can take it!

Thanks,

Gary

 

[Alastair MacFarlane]

Gary

The simple answer is to look up articles on Microsoft's
website on QBF (Query By Form). The query that the report
is based upon will have references to your form in the
criteria section e.g. Forms!FormA!ControlA. Rather than me
going through it step-by-step I would suggest that you
look at these articles and come back to the group with any
additional problems. Good luck!

Alastair MacFarlane

 

[Steve Schapel]

Gary,

Assuming you would only ever want to select one value for each of the 3
fields, or alternatively leaving the criteria box blank means "All"...
Firstly, why do you need a second control on the form associated with
each field criteria selection? You can simply refer directly to the
combobox. Make your report based on a query, and in the query criteria
of each of the three fields, put the equivalent of...
Like Nz([Forms]![NameOfForm]![NameOfCombobox],"*")

 

[Gary Schuldt]

Thanks Alastair!

QBF, eh? I didn't know what it was called, so having an handle will help.

Gary

 

[Gary Schuldt]

Steve,

thanks!

re: Why a second control?

No good reason, just Fear (!) that the combo box wouldn't continue to
display its selection when I went on to the next one.

I think I have a good idea how to proceed.

Once I get that to work in our environment, I plan to allow the user to name
their combination selection, store it somewhere, and make it available as an
option to run a saved query.

Small steps, yes; others have probably done it thousands of times, but no
one's paying me to do this, and I'm gradually getting more confident with
Access.

Gary

Gary,

Assuming you would only ever want to select one value for each of the 3
fields, or alternatively leaving the criteria box blank means "All"...
Firstly, why do you need a second control on the form associated with
each field criteria selection? You can simply refer directly to the
combobox. Make your report based on a query, and in the query criteria
of each of the three fields, put the equivalent of...
Like Nz([Forms]![NameOfForm]![NameOfCombobox],"*")

--
Steve Schapel, Microsoft Access MVP

Someone must have done this before:

I need a Form, or a Wizard for a Form, that will support parametric
generation of a query or report.

A simple example: Here are 3 fields of selection interest in my
DB--ProductName, StoreType, and RegionName.

What I want to see are displays like "a list of all ProductNames stocked by
StoreType="Convenience" and RegionName="NW".

So I'd like the Selection Form to show a drop-down list for all values of
each of those 3 fields, and associated with each dropdown would be another
control showing the value the user clicked on.

After they'd made their selections, there'd be a Run button to run the query
/ report.

OTOH, maybe this is so simple that there's no need for a template or wizard
to walk me through this! Just tell me, I can take it!

Thanks,

Gary