Drop down list inside a query

[Guest]

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks

 

[Guest]

Create a form not linked to any table (AKA unbound form) named frmSearch. On
this form put a combo box named something cboSearch. Make the record source
for the combo box a query or list that has the search terms. Also put a
button on the form that will run the query. The Command Button wizard will
guide you through it under Miscellaneous, Run Query.

In the query put the following in the appropriate criteria box:
[Forms]![frmSearch]![cboSearch].[Value]

You select something from the combo box on the form and then run the query.
The form needs to stay open; however, it could be minimized or even hidden.

 

[John W. Vinson]

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks

The user should in general never see a query datasheet, or "run" a query; and
to have a combo box in the query provide a criterion for that query itself is
circular logic!

Consider using a continuous Form, with an unbound combo box in its header.
Base the form on a query referencing

Forms!formname!comboboxname

as a criterion, and requery the form in the combo's afterupdate event.

John W. Vinson [MVP]

 

[Guest]

Hi Jerry,

Need your help!!! I have an additional question to this one and thought you
might be able to help me.

I created all this. Works ok with text. But the query does not return
anything when the value has other characters such as (). When the field
(defined as text) has parenthesis such as customer (revised) it will return
nothing when it should.

I am wondering if there is something I can put in the criteria of the query
so I can choose from the drop down values that include parenthesis ().

Thanks in advance for your help!

Create a form not linked to any table (AKA unbound form) named frmSearch. On
this form put a combo box named something cboSearch. Make the record source
for the combo box a query or list that has the search terms. Also put a
button on the form that will run the query. The Command Button wizard will
guide you through it under Miscellaneous, Run Query.

In the query put the following in the appropriate criteria box:
[Forms]![frmSearch]![cboSearch].[Value]

You select something from the combo box on the form and then run the query.
The form needs to stay open; however, it could be minimized or even hidden.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks

 

[Guest]

There's three ways to fix something like this:

1. Clean up the data and restrict what users can enter.

2. Base the row source of the combo box on the field in question. That way
it will show all possible entries to that field. Use a SELECT DISTINCT clause
in the SQL to remove duplicates.

3. Use something like below in the query. The problem is is someone selects
"a", anything with an "a" in it will be returned.

Like "*" & [Forms]![frmSearch]![cboSearch].[Value] & "*"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry,

Need your help!!! I have an additional question to this one and thought you
might be able to help me.

I created all this. Works ok with text. But the query does not return
anything when the value has other characters such as (). When the field
(defined as text) has parenthesis such as customer (revised) it will return
nothing when it should.

I am wondering if there is something I can put in the criteria of the query
so I can choose from the drop down values that include parenthesis ().

Thanks in advance for your help!

Create a form not linked to any table (AKA unbound form) named frmSearch. On
this form put a combo box named something cboSearch. Make the record source
for the combo box a query or list that has the search terms. Also put a
button on the form that will run the query. The Command Button wizard will
guide you through it under Miscellaneous, Run Query.

In the query put the following in the appropriate criteria box:
[Forms]![frmSearch]![cboSearch].[Value]

You select something from the combo box on the form and then run the query.
The form needs to stay open; however, it could be minimized or even hidden.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks

 

[Guest]

Thanks much Jerry!

Hi Jerry,

Need your help!!! I have an additional question to this one and thought you
might be able to help me.

I created all this. Works ok with text. But the query does not return
anything when the value has other characters such as (). When the field
(defined as text) has parenthesis such as customer (revised) it will return
nothing when it should.

I am wondering if there is something I can put in the criteria of the query
so I can choose from the drop down values that include parenthesis ().

Thanks in advance for your help!

Create a form not linked to any table (AKA unbound form) named frmSearch. On
this form put a combo box named something cboSearch. Make the record source
for the combo box a query or list that has the search terms. Also put a
button on the form that will run the query. The Command Button wizard will
guide you through it under Miscellaneous, Run Query.

In the query put the following in the appropriate criteria box:
[Forms]![frmSearch]![cboSearch].[Value]

You select something from the combo box on the form and then run the query.
The form needs to stay open; however, it could be minimized or even hidden.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks

 

[Guest]

Thanks Jerry!!!

Hi Jerry,

Need your help!!! I have an additional question to this one and thought you
might be able to help me.

I created all this. Works ok with text. But the query does not return
anything when the value has other characters such as (). When the field
(defined as text) has parenthesis such as customer (revised) it will return
nothing when it should.

I am wondering if there is something I can put in the criteria of the query
so I can choose from the drop down values that include parenthesis ().

Thanks in advance for your help!

Create a form not linked to any table (AKA unbound form) named frmSearch. On
this form put a combo box named something cboSearch. Make the record source
for the combo box a query or list that has the search terms. Also put a
button on the form that will run the query. The Command Button wizard will
guide you through it under Miscellaneous, Run Query.

In the query put the following in the appropriate criteria box:
[Forms]![frmSearch]![cboSearch].[Value]

You select something from the combo box on the form and then run the query.
The form needs to stay open; however, it could be minimized or even hidden.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I would like to add a drop down list from data in a table to a query so when
the query is ran the user can use the drop down list to select an item for
the query to search by.

Thanks