View Single Post
  #4  
Old June 3rd, 2010, 02:38 PM posted to microsoft.public.access.queries
venajoe
external usenet poster
 
Posts: 7
Default Parameter queries that allow users to choose items from a list

Groeten,

I tried what you recommended, and I've run into a difficulty. I created a
form, named frmChooseAuthorLastNameQry, and created an unbound combo box
named Combo0. When I attempt to specify the rowsource property of this
combobox as you indicated, here's what the query builder (in SQL view)
returned:

SELECT DISTINCT tblAuthorLastNames.LastName AS Expr1
FROM tblAuthorLastNames
WHERE
((([tblAuthorLastNames].[LastName])=[forms]![frmChooseAuthorLastNameQry]![Combo0]));

When I try to select the down arrow to open the drop down list, I get a
parameter box asking me to type in the last name, which is exactly what I was
trying to avoid! I don't know enough about the proper SQL syntax to make
this work properly, but I'm sure you do!

Appreciate your patience walking me thru this--figuring out how to do this
would help me create better searches in several databases I use regularly.

Thanks again!

Joe
--
Joe Venable
Virginia Beach, VA


"XPS35" wrote:

=?Utf-8?B?dmVuYWpvZQ==?= wrote:


Dear Access 2007 VBA Gurus,

I have a "Worship Song" database. The tables a
tblWorshipSongs
tblAuthorFirstNames
tblAuthorLastNames
tblCopyrightHolders
tblKeys
tblTempos
tblSongThemes
tblSongTypes

I would like to create parameter queries that allow users to choose their
parameter from a list, preferably the entries from one of the tables. For
example, suppose I wanted to query all the songs by a particular author's
last name. I don't just want users to type in a last name, in which case
they might misspell it and return no records, or type in an entry that is not
already in the table (and again get no records returned). I would like to
limit the user's choices to the last names already in the last names table
and allow the user to choose from a drop-down list of those last names to run
the query. I am a VBA beginner, and I have made a few attempts at this, but
so far, no luck--help!
--
Joe Venable
Virginia Beach, VA


A parameter query is not the right way to do this.

Make a form with an unbound combobox. The rowsource of the combo would
look like:
SELECT DISTINCT LastName FROM tblAuthorLastNames (why first names and
last names in seperate tables?).

In the condition part of a query you can refer to the combo
(forms!yourform!yourcombo).

--
Groeten,

Peter
http://access.xps350.com

.