Using Combo Boxes as a Source for Parameter Queries

[venajoe]

Access Gurus,

I often encounter a situation where I want to run a select (parameter) query
on a particular table, but instead of having the user type in their own
parameter, I'd like for them to be able to choose, from a combo box that
auto-completes, the actual item in the underlying table, then use this string
as the basis for the parameter query. I want to limit the parameters to
already-extant items in the underlying table and so avoid close-but-not-exact
matching problems by free-typing the parameter.

 

[KARL DEWEY]

Use this as criteria --
[Forms]![YourFormName]![ComboName]

 

[John Spencer]

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
http://office.microsoft.com/en-us/access/HA011730581033.aspx

Or for another example
http://allenbrowne.com/ser-62.html

A brief quote from a John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria) with a
Combo Box control (cboCrit) on it. Use the combo box wizard to select the
table for the selections, and be sure that the bound field of the combo is the
value you want to use as a criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that form or
report, the user can enter the criterion and view the results in one simple
operation!

End quote

One thing to remember is that the form MUST be open in order for this to work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County