create a query that provides a drop down list to choose from

[john mcmichael]

I need to create a Query that when run, provides a menu of options to choose
from. These options can come from an existing table or I can type them in
while creating the query. Once the option is selected, the query should then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option for
people in this database but I do not know how to create this type of feature.

Thanks

 

[Allen Browne]

It is not possible to get a drop-down list into the Parameter box offered by
a query.

If you create a form, and put the combo on the form, you can refer to it in
your query. For example, you could type this into the Criteria row in your
query:
[Forms].[Form1].[Combo65]

 

[Gwen H]

I have done something very similar using forms, and it works perfectly.
Create a form that includes either a list box or combo box field that lists
the "menu of options". Add a button that invokes your query. Then in your
query, in the field you're trying to filter on, use the following criteria:

[Forms]![formName]![listORcomboBOXname]

If you need to generate a report, base the report on the query. The button
on your form would need to invoke the report rather than the query.

You can also have the form open automatically when the database is opened,
if people who don't know Access are going to need to run this query and you
don't want them messing around with your tables and queries.

 

[john mcmichael]

I have folks here at the office telling me that they have been able to do
this in previous versions of Access but htey can't remember how. Is this
something that has been removed from the software?

Thanks again.

It is not possible to get a drop-down list into the Parameter box offered by
a query.

If you create a form, and put the combo on the form, you can refer to it in
your query. For example, you could type this into the Criteria row in your
query:
[Forms].[Form1].[Combo65]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to create a Query that when run, provides a menu of options to
choose
from. These options can come from an existing table or I can type them in
while creating the query. Once the option is selected, the query should
then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option
for
people in this database but I do not know how to create this type of
feature.

Thanks

 

[john mcmichael]

Thank you both Allen and Gwen, but it doesnt seem to work. I have a form
called Sample and a combo box labeled Combo 5. When I open the form it work
properly, listing the options and letting me choose one. However, when
Itype the criteria [Forms]![Sample]![Combo5], the result is just a box that
waits for me type the criteria not providing me a list of options. ANy idea
where I'm falling off track?

I have done something very similar using forms, and it works perfectly.
Create a form that includes either a list box or combo box field that lists
the "menu of options". Add a button that invokes your query. Then in your
query, in the field you're trying to filter on, use the following criteria:

[Forms]![formName]![listORcomboBOXname]

If you need to generate a report, base the report on the query. The button
on your form would need to invoke the report rather than the query.

You can also have the form open automatically when the database is opened,
if people who don't know Access are going to need to run this query and you
don't want them messing around with your tables and queries.

I need to create a Query that when run, provides a menu of options to choose
from. These options can come from an existing table or I can type them in
while creating the query. Once the option is selected, the query should then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option for
people in this database but I do not know how to create this type of feature.

Thanks

 

[Allen Browne]

No. The parameter box has never supported a drop-down list. It is not a
serious interface, is should be avoided if you are writing a serious
application.

The form must be open before the query if you expect the query to read the
value from the combo on the form.

If you have the form opened, and a value chosen in the combo, and then open
the query and still get asked for the parameter, then the reference to the
query is not typed correctly, e.g. there might be a space somewhere.

If this is a crosstab query, you must declare the parameter in the query.
It's a good idea to do that anyway. Choose Parameters on the query menu,
enter the full name of the parameter, and indicate the data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have folks here at the office telling me that they have been able to do
this in previous versions of Access but htey can't remember how. Is this
something that has been removed from the software?

Thanks again.

It is not possible to get a drop-down list into the Parameter box offered
by
a query.

If you create a form, and put the combo on the form, you can refer to it
in
your query. For example, you could type this into the Criteria row in
your
query:
[Forms].[Form1].[Combo65]

message

I need to create a Query that when run, provides a menu of options to
choose
from. These options can come from an existing table or I can type them
in
while creating the query. Once the option is selected, the query
should
then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option
for
people in this database but I do not know how to create this type of
feature.

 

[john mcmichael]

Thanks Allen...that will work!! I appreciate your help and happy holidays to
you.

No. The parameter box has never supported a drop-down list. It is not a
serious interface, is should be avoided if you are writing a serious
application.

The form must be open before the query if you expect the query to read the
value from the combo on the form.

If you have the form opened, and a value chosen in the combo, and then open
the query and still get asked for the parameter, then the reference to the
query is not typed correctly, e.g. there might be a space somewhere.

If this is a crosstab query, you must declare the parameter in the query.
It's a good idea to do that anyway. Choose Parameters on the query menu,
enter the full name of the parameter, and indicate the data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have folks here at the office telling me that they have been able to do
this in previous versions of Access but htey can't remember how. Is this
something that has been removed from the software?

Thanks again.

It is not possible to get a drop-down list into the Parameter box offered
by
a query.

If you create a form, and put the combo on the form, you can refer to it
in
your query. For example, you could type this into the Criteria row in
your
query:
[Forms].[Form1].[Combo65]

message
I need to create a Query that when run, provides a menu of options to
choose
from. These options can come from an existing table or I can type them
in
while creating the query. Once the option is selected, the query
should
then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option
for
people in this database but I do not know how to create this type of
feature.

 

[Carolyn :)]

I also tried doing what Allen & Gwen suggested, and also had no luck, can any
shed some light on this. It would be a really handy tool to be able to use a
drop down box in a query/Report, when you ahve many options to choose from.

Thank you both Allen and Gwen, but it doesnt seem to work. I have a form
called Sample and a combo box labeled Combo 5. When I open the form it work
properly, listing the options and letting me choose one. However, when
Itype the criteria [Forms]![Sample]![Combo5], the result is just a box that
waits for me type the criteria not providing me a list of options. ANy idea
where I'm falling off track?

I have done something very similar using forms, and it works perfectly.
Create a form that includes either a list box or combo box field that lists
the "menu of options". Add a button that invokes your query. Then in your
query, in the field you're trying to filter on, use the following criteria:

[Forms]![formName]![listORcomboBOXname]

If you need to generate a report, base the report on the query. The button
on your form would need to invoke the report rather than the query.

You can also have the form open automatically when the database is opened,
if people who don't know Access are going to need to run this query and you
don't want them messing around with your tables and queries.

I need to create a Query that when run, provides a menu of options to choose
from. These options can come from an existing table or I can type them in
while creating the query. Once the option is selected, the query should then
complete. I know that I can create a prompt for info using [] around a
statement in a criteria field. Choosing from a list is a better option for
people in this database but I do not know how to create this type of feature.

Thanks

 

[Carolyn :)]

Thank you both Allen and Gwen, but it doesnt seem to work. I have
a form called Sample and a combo box labeled Combo 5. When I open
the form it work properly, listing the options and letting me
choose one. However, when Itype the criteria
[Forms]![Sample]![Combo5], the result is just a box that waits for
me type the criteria not providing me a list of options. ANy idea
where I'm falling off track?

I also tried doing what Allen & Gwen suggested, and also had no luck,
can any shed some light on this. It would be a really handy tool to
be able to use a drop down box in a query/Report, when you ahve many
options to choose from.

The form has to be opened and the choice made *before* you open the query or
report that references it. Is that what you tried?

Hi Rick,

Yes that is what I did. I typed it in the criteria same way as suggested, I
had the form open and it still didn't work. I am unsure what I did, or what
I am doing wrong?

 

[Dabears70]

Was this problem ever solved because I am having the same issue. Can somebody
please help me get a drop down menu in a parameter query. There are so many
items to chose from for the parameter that it is time saving to have a drop
down list.

Please help!

Thank you,
--
Nick

:

Thank you both Allen and Gwen, but it doesnt seem to work. I have
a form called Sample and a combo box labeled Combo 5. When I open
the form it work properly, listing the options and letting me
choose one. However, when Itype the criteria
[Forms]![Sample]![Combo5], the result is just a box that waits for
me type the criteria not providing me a list of options. ANy idea
where I'm falling off track?
I also tried doing what Allen & Gwen suggested, and also had no luck,
can any shed some light on this. It would be a really handy tool to
be able to use a drop down box in a query/Report, when you ahve many
options to choose from.

The form has to be opened and the choice made *before* you open the query or
report that references it. Is that what you tried?

Hi Rick,

Yes that is what I did. I typed it in the criteria same way as suggested, I
had the form open and it still didn't work. I am unsure what I did, or what
I am doing wrong?