A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter queries that allow users to choose items from a list



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2010, 08:41 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

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
  #2  
Old May 25th, 2010, 09:07 PM posted to microsoft.public.access.queries
xps35
external usenet poster
 
Posts: 22
Default Parameter queries that allow users to choose items from a list

=?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

  #3  
Old May 25th, 2010, 09:17 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Parameter queries that allow users to choose items from a list

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


  #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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.