Can I get drop down lists to select out of for queries?

[Guest]

I have one table that holds all information, which is linked to external
software. I would like to use Access as a query and report generating tool.

Is there a way to build an simple application within access that allows me
to pick out of a drop down list what I want included in a query and how it is
displayed (avg., sum, etc.)?

I actually need to be able to select attributes, not field names...

 

[Jeff Boyce]

Bryan

I'm not exactly clear on what you want to create ... or why you wouldn't
just use the query design view?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for your reply.

The query/design view works well for building the general query, but I would
like to filter what is included in the query.

So, I have a large table that has 20 farmer customers' names, along with all
of their farm names, and the average corn yield of each farm field. So, I
would like to be able to generate a report that shows:

Client Name___________Farm Name___________Avg Yield
Joe Farmer Home Farm 184.2 bu/Acre

However, I want to be able to select out of a drop down box which farmer's
data is included in the report, without having to type in "Joe Farmer" in the
criteria.

Does that help?

 

[Guest]

Another thought to maybe clarify....

Once I have the report built, can I have a drop down box in a report and
have the report change after I select a different farmer name in the box?

 

[fredg]

Another thought to maybe clarify....

Once I have the report built, can I have a drop down box in a report and
have the report change after I select a different farmer name in the box?

Well, this is not what you asked for in your initial post.
Now you wish to be able to select the name of the person you wish to
report on, using a drop-down list.
You cannot use a drop-down list directly in the query (that is used as
the report's recordsource).

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
FarmerID field and the Farmer Name.
Name the Combo Box 'FindFarmer'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"


*** Optional if you wish to limit records by date
*** Add 2 unbound text controls to the form
*** Set their format to a valid date format.
*** Name them "StartDate" and "EndDate"
*** Optional

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source [FarmerID] field
criteria line write:
forms!ParamForm!FindFarmer

*** Optional if you wish to limit records by date
*** As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
*** Optional

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Farmer and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Guest]

Thanks for your reply! Yes, that is what I am wanting to do. I want to have a
general menu of commonly requested reports, and then be able to choose which
client to report on.

I will try your directions and see if I can get it to work, and let you
know. Please keep in touch w/this post.

Thanks much-
Bryan

Another thought to maybe clarify....

Once I have the report built, can I have a drop down box in a report and
have the report change after I select a different farmer name in the box?

Well, this is not what you asked for in your initial post.
Now you wish to be able to select the name of the person you wish to
report on, using a drop-down list.
You cannot use a drop-down list directly in the query (that is used as
the report's recordsource).

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
FarmerID field and the Farmer Name.
Name the Combo Box 'FindFarmer'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"


*** Optional if you wish to limit records by date
*** Add 2 unbound text controls to the form
*** Set their format to a valid date format.
*** Name them "StartDate" and "EndDate"
*** Optional

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source [FarmerID] field
criteria line write:
forms!ParamForm!FindFarmer

*** Optional if you wish to limit records by date
*** As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
*** Optional

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Farmer and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Guest]

Thank you! That worked.

Now, hopefully I can explain this well enough. As mentioned above, I have a
very large linked table that holds all of my information. This includes
Client Name, Farm Name, and Field Name (what they call the field within the
farm, such as Home Farm, North Field). It also holds fertility, soil types,
and crop yield as part of the table. This table is comprised of multiple
customers.

I have a base set of reports that I have built, which as of right now
reports on the entire table. (except for my test that you helped with).

Do I have to build a 'ParamForm' for each report, or is there a way that I
could build a form that allows me to: First, pick the client I want to report
on, then pick which farm of his, and then pick the field, and then finally
which report?

I would like to be able to choose between allowing the entire table in the
query, narrowing it down to one field, or ultimately, being able to choose
multiple clients on one report (say John, Bill, Frank) but not the other 30.

Am I making any sense?!? Thanks again for your help. I appreciate it!
Bryan

Another thought to maybe clarify....

Once I have the report built, can I have a drop down box in a report and
have the report change after I select a different farmer name in the box?

Well, this is not what you asked for in your initial post.
Now you wish to be able to select the name of the person you wish to
report on, using a drop-down list.
You cannot use a drop-down list directly in the query (that is used as
the report's recordsource).

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
FarmerID field and the Farmer Name.
Name the Combo Box 'FindFarmer'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"


*** Optional if you wish to limit records by date
*** Add 2 unbound text controls to the form
*** Set their format to a valid date format.
*** Name them "StartDate" and "EndDate"
*** Optional

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Query that is the Report's Record Source [FarmerID] field
criteria line write:
forms!ParamForm!FindFarmer

*** Optional if you wish to limit records by date
*** As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
*** Optional

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Farmer and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.