Using a combo box or list box to select criteria for a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use a combo box or a list box to select
the criteria for a query or report?
 
Is it possible to use a combo box or a list box to select
the criteria for a query or report?
Click to expand...

You'll need to use a form to do this.
In this example, we'll use a combo box to select a particular
Customer.
Make a new unbound form.
Add a combo box that will hold the CustomerID and the Customer Name
fields.
Hide the ID field by setting the Combo's ColumnWidths property to:
0";1"
Set the Combo's Column Count to 2
Set the Combo's Bound Column to 1

Add a command button to the form.
Code the button's Click event:

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

Name this form "ParamForm"

In the Query, on the CustomerID field criteria line write:

forms!ParamForm!ComboBoxName

Make sure the Combo Box Bound Column is the
same DataType as the Query CustomerID field.

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

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

To use the query as the recordsource of a report, all is the same as
above, EXCEPT... code the Form's Command button:

Me.Visible = False

Code the Report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

Do NOT open the Form.
Run the report. The Report will open the form.
Select the Customer. Click the Command button.
The Report will display. When you close the Report, the Form will also
close.
 
Is it possible to use a combo box or a list box to select
the criteria for a query or report?
Click to expand...

Yes. Use an unbound combo box (its Control Source should be blank);
let's say you have a form frmCrit with a combo box cboCriterion. Use a
Query with a criterion of

=Forms![frmCrit]![cboCriterion]

It's convenient to put a command button on frmCrit to launch a Form
(for onscreen display) or Report (for printing) of the records
retrieved by the query.
 
-----Original Message-----
Click to expand...
select the criteria for a query or report?I would also like to do this. The query to launch my
report would have two parameters, among other
things. "Enter the service Week".
That is just a number and no big deal to type it. My next
parameter is "Enter a Service Schedule" For this one the
user has three choices 1. Monthly 2. Bi-Monthly (even) 3.
Bi-Monthly (odd). I would prefer the user be able to
select their choice instead of typing their choice.
When these choices are made. After these options are
chosen 5 to 10 reports would print.
Should I attempt to follow the instructions in the first
response? it sounds kind of complicated.

Attn John Vinson: My database is going well. I'm not sure
I am "Normal" yet but I am working on it.
 
Back
Top