Using a form to enter 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]

Yes you'd use a form. Create a form (I'll call it frmParameters), with no
recordsource. You can add all the controls you need to gather the criteria
for your query.

Given your example, I'd put a combobox and a command button.

Set the rowsource of the combobox to the table that contains your company
names (I'll assume tblCompanies). You can use the combobox wizard to build
it for you.

You can then change the criteria on your query from [Enter company name] to
instead refer to the combobox on your parameter form e.g.
Forms!frmParameters!cboCompanyName
(cboCompanyName is the name of the combobox).

You can add code in the On Click property of the command button to open your
query. Actually you should build a form or report based on this query, then
your button could open the form/report
DoCmd.OpenReport "rptSomeName", acPreview

 

[Guest]

Accessor,

Joan's response is an excellent solution when the user will enter one or
more criteria. It won't handle cases though where some of the criteria is
left blank. In these cases, you can build an SQL string by looping through
the controls on the criteria form, and passing the string as a parameter to
the OpenForm method.

See my response to Zanstemic under the topic "list form" on this forum.

Sprinks