Combo Box in a query?

[Kerman]

Is there a way to have a combo box in a query, say in the criteria line. I
want to run a query on lets say "Size". I have 20 different sizes to chose
from. I know how to use the "Like" function in criteria but you have to
spell out size exactly for it to work. Typing in "Extra Large other than
Sev" would be too time consuming. Any ideas...Thanks...Randy

 

[Tom Starks]

Hi Randy,

Well, I have a solution if you are looking to do this in a
client-side form in Access2002. Currently, I've got a post
on looking to find out how to do in on a DAP. Help!!

But back to your problem. Create one unbound combobox with
your 20 or so different sizes. Create another combobox
field - or a single value unbound field if the return
value "per size" is just one value - on the same form that
has as it's rowsourcetype as "table/query" make its
rowsource the name of a query that you have already
created to lookup a value based on a particular size. Then
in the "After Update" event of the first field (the one
with the sizes) enter this in the sub:

Me!ComboBoxFieldName = Null
Me!ComboBoxFieldName.Requery

where "ComboBoxFieldName" is the name of the field or
combobox that the result(s) are going to be returned in.

Then create your query in querybuilder using something
that, in my database, looks like this:

SELECT Messages.Message ' Comment: ' "Message" is the
table column name containing the values that you want
returned based on the size selected in the other combobox
FROM MessageTypes INNER JOIN Messages ON
MessageTypes.MessageTypeID=Messages.MessageTypeID
WHERE ((MessageTypes.MessageType=forms!MainForm!combo34));

In this example, "MainForm" is the name of the form
and "combo24" is the name of the combobox with all
the "key values" in it. In yours this would be where you
put the sizes. "Messages" is the table with all the
message values in it along with their individual id's and
the id's (not names) of their "types". And "MessageTypes"
is the table name of the table where all the names and
ID's of message types are stored. That's why I had to do
the inner join. To match the values in two different
tables using a common key (the "messagetypeid" value).

Hope this has been helpful. It's even easier if you are
just trying to get one value per size and everything is in
one table:

Select tablename.lookupfieldvalue
from tablename
where tablename.keyfieldvalue = forms!formname!
comboboxname;

Take care,

Tom

 

[Dennis Schmidt]

Hi Randy,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

Sorry, no combo boxes for the criteria line in a query. Consider creating a
datasheet form and use the FilterByForm functionality. It creates a combo
box with all possible values for you.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support

 

[Randy]

Well I tried this approach, but I got totally lost. Thanks any way. I
guess another approach would be to create a query for each size...Randy