Form for Inputing query parameters.

[Karthik]

Hi All
I am a new user of Access. And am posting this topic after having
searched this group for a long time for what I want.

I have a big sales database (Table: Master Sales Report) which has
names of salesmen and the products sold by them in addition to many
other columns. I run a query (Query: Get Data), on this which asks me
for [name of salesman] and [product] and gives me the sales report for
that salesman and product in make table query (Table: Report Extract).

Every time I need a report I manfully run the query. What I want is a
form which will have two data entry fields [name of salesman] and
[product] with a dropdown for selection. This drop down should refer to
the 2 tables that I have for name of salesmen (Table: name) and product
(Table: Product). Once I select these 2 parameters and click a Ok
button on the form It should pick run the query with the parameters and
generate Table: Report Extract.


Thanks for taking time to help

Thanks and Regards
Karthik Bhat

 

[Phil]

I have done a similar thing recently

create a form based on your table with the two fields you want to query

in the query fot the fields you want add the parameters

[Forms]![YourForm]![NameofFieldonForm] to criteria

add a button bound to the report based on this query to the form

 

[Larry Linson]

I am a new user of Access. And am
posting this topic after having searched
this group for a long time for what I want.

I have a big sales database (Table: Master
Sales Report) which has names of salesmen
and the products sold by them in addition to many
other columns. I run a query (Query: Get Data),
on this which asks me for [name of salesman] and
[product] and gives me the sales report for
that salesman and product in make table query
(Table: Report Extract).

Every time I need a report I manfully run the
query. What I want is a form which will have
two data entry fields [name of salesman] and
[product] with a dropdown for selection. This
drop down should refer to the 2 tables that I
have for name of salesmen (Table: name) and
product (Table: Product). Once I select these
2 parameters and click a Ok button on the
form It should pick run the query with the
parameters and generate Table: Report Extract.

Help me understand: Why do you need to make a Table? Why do you not use a
query as the Record Source of the Report that produces the same data that
you use to make the table ?

The following will require some skills with Visual Basic for Applications
(VBA), the programming language of Access (and some other software):

You could create a Form, on which you have two Combo boxes. One combo box
would have as its Row Source, a Query or SQL statement that selected the
names of the salesmen from Table Name, the other would have as its Row
Source a Query or SQL statement that selects the product identification.
Once you have selected these, the user clicks a Command Button to run the
report.

In the Click event of the Command Button, you write VBA code that picks up
the selected salesman and product from the respective Combo Boxes to build
the WhereCondtion of a DoCmd.OpenReport. The report has a Query which does
not have Parameters (that is, does not "ask" for the Criteria) -- the
criteria will automatically be applied because of the WhereCondition arguent
of the DoCmd.OpenReport method.

Air code (untested) example:

Dim strWhere as String

strWhere = "[Salesman] = """ & Me.cboSalesman & """ AND [Product] =
""" & Me.cboProduct & """"

DoCmd.OpenReport(rptYourReport,,,strWhere)

That should do what you want when you substitute your actual Field, Control,
and Report names.

Larry Linson
Microsoft Access MVP