Parameter queries that allow users to choose items from a list

Dear Access 2007 VBA Gurus,

I have a "Worship Song" database.  The tables are:
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
0
Utf
5/25/2010 7:41:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1841 Views

Similar Articles

[PageSpeed] 0

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

> 
> Dear Access 2007 VBA Gurus,
> 
> I have a "Worship Song" database.  The tables are:
> 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

0
XPS35
5/25/2010 8:07:29 PM
XPS35 wrote:
> =?Utf-8?B?dmVuYWpvZQ==?= wrote:
>
> 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).

.... which makes it a parameter query ... ;-)
-- 
HTH,
Bob Barrows


0
Bob
5/25/2010 8:17:25 PM
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 are:
> > 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
> 
> .
> 
0
Utf
6/3/2010 1:38:16 PM
Reply:

Similar Artilces: