Parameter queries that allow users to choose items from a list
in [Queries]
|
From: venajoe on 25 May 2010 15:41 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
From: XPS35 on 25 May 2010 16:07 =?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
From: Bob Barrows on 25 May 2010 16:17 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
From: venajoe on 3 Jun 2010 09:38 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 > > . >
|
Pages: 1 Prev: Parameter in Union Query Next: Convert string value to numeric value |