Using a form to enter a criteria for a query

[Guest]

I currently have a field in an Access query, which accepts an input as a
criteria and executes the query based on that input. How can I know automate
the input entry process? Is a form the best method to do so? If so, how?

For example, I have a table which lists all the individuals working within a
series of companies. My query then allows me to input a company name as a
criteria on the "company field" and display the resulting list of individuals
who work at that company.

Basically, instead of having to type in the input as a criteria, can I use a
form?

 

[Joan Wild]

This was answered in the forms newsgroup. Please don't multipost messages.

 

[Guest]

Add a combo box and a command button to an unbound dialogue form. Set the
combo box's RowSource so that all companies are listed alphabetically, e.g.

SELECT Company FROM Companies ORDER BY Company;

Open the query, or better still a form or report based on the query, with
the button.

In the query, in design view, change the parameter in the criteria row of
the Company column to a reference to the combo box, e.g.

Forms![frmCompanyDlg]![cboCompanies]

where frmCompanyDlg is the name of the form and cboCompanies is the name of
the combo box. On the form the user then just selects a company from the
combo box's list and clicks the button.

Ken Sheridan
Stafford, England

 

[fredg]

I currently have a field in an Access query, which accepts an input as a
criteria and executes the query based on that input. How can I know automate
the input entry process? Is a form the best method to do so? If so, how?

For example, I have a table which lists all the individuals working within a
series of companies. My query then allows me to input a company name as a
criteria on the "company field" and display the resulting list of individuals
who work at that company.

Basically, instead of having to type in the input as a criteria, can I use a
form?

The query is the end result of this or is the query the record source
of a report?

If the query is the end result, then:

Make a new unbound form.
Add a combo box that will show the CompanyID field.
Make sure the Combo Box Bound Column is the
same DataType as the CompanyID field.
Add a command button.

Code the button's Click event:

DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name

Name this form "ParamForm"

Code the Query CompanyID field criteria line
forms!ParamForm!ComboBoxName

Open the form.
Find the CompanyID in the combo box.
Click the command button.

The query will display just those records selected.
The Form will close.