Query Parameters using a listbox or comobox...

Question

I have read all of the related questions on the board and maybe I'm just brain dead but this is what I want to do...

1. I'm using Access 2003
2. My database that I'm building is for a searchable music discogrophy.
3. I have gotton all of my table in line and have created my first queries but would like to on certain ones to have the user select from a list in order to set the query off. What I mean by this is for example if the user wishes to search the database for all recording sessions that took place in a certain city I want the parameter for the location query to select the cities that are currently in the locations table and show that list and allow them to click on the city they desire and then the query would produce the results.
4. I have followed seveal of the examples set forth here and just can not seem to follow them or get them to work. When I open the query and type "Chicago" as the parameter and run the query I get the perfect result but am not able to figure out how to get the list to choose from and then get the same results.
5. I have no VBA or SQL abilities for the record

Any help here would be very grateful. Thanks.

Answer 1

Well, first of all, the query does not need to be a Totals query.  It should look like:

SELECT tbl01RecDatesList.[Recording Dates], tbl03SongMasterNbr.[Song Title Master Nbr], tbl02SongList.[Song Title], [Q1 Recording Dates and Location Match].[Recording Locations]

FROM (tbl03SongMasterNbr INNER JOIN (tbl02SongList INNER JOIN [tbl02aSong-MasterNbrList] ON tbl02SongList.[Song Title Prime Key] = [tbl02aSong-MasterNbrList].[Song Title PK]) ON tbl03SongMasterNbr.[Song Title Master Nbr Primary Nbr] = [tbl02aSong-MasterNbrList].[Song Master Nbr PK]) INNER JOIN ((tbl01RecDatesList INNER JOIN [Q1 Recording Dates and Location Match] ON tbl01RecDatesList.[Recording Dates] = [Q1 Recording Dates and Location Match].[Recording Dates]) INNER JOIN [tbl03a Song Master Nbr - Recording Dates List] ON tbl01RecDatesList.[Reording Dates Primary Key] = [tbl03a Song Master Nbr - Recording Dates List].[Recording Dates PK]) ON tbl03SongMasterNbr.[Song Title Master Nbr Primary Nbr] = [tbl03a Song Master Nbr - Recording Dates List].[Song Master Nbr PK]

WHERE ((([Q1 Recording Dates and Location Match].[Recording Locations])=[Forms]![frmLocationSearch]![lstCitySearch]))

ORDER BY tbl01RecDatesList.[Recording Dates], tbl03SongMasterNbr.[Song Title Master Nbr];

What's in the Recording Locations field?  If it is text containing the city name, then the Row Source of your combo box should be:

SELECT [tbl04 Recording Locations List].[Recording Locations] FROM [tbl04 Recording Locations List] ORDER BY [Recording Locations];

You should also change the Column Count to 1, and fix the Column Widths property.

John Viescas, author

Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/

(Paris, France)

Answer 2

The Query in SQL View

SELECT tbl01RecDatesList.[Recording Dates], tbl03SongMasterNbr.[Song Title Master Nbr], tbl02SongList.[Song Title], [Q1 Recording Dates and Location Match].[Recording Locations]

FROM (tbl03SongMasterNbr INNER JOIN (tbl02SongList INNER JOIN [tbl02aSong-MasterNbrList] ON tbl02SongList.[Song Title Prime Key] = [tbl02aSong-MasterNbrList].[Song Title PK]) ON tbl03SongMasterNbr.[Song Title Master Nbr Primary Nbr] = [tbl02aSong-MasterNbrList].[Song Master Nbr PK]) INNER JOIN ((tbl01RecDatesList INNER JOIN [Q1 Recording Dates and Location Match] ON tbl01RecDatesList.[Recording Dates] = [Q1 Recording Dates and Location Match].[Recording Dates]) INNER JOIN [tbl03a Song Master Nbr - Recording Dates List] ON tbl01RecDatesList.[Reording Dates Primary Key] = [tbl03a Song Master Nbr - Recording Dates List].[Recording Dates PK]) ON tbl03SongMasterNbr.[Song Title Master Nbr Primary Nbr] = [tbl03a Song Master Nbr - Recording Dates List].[Song Master Nbr PK]

GROUP BY tbl01RecDatesList.[Recording Dates], tbl03SongMasterNbr.[Song Title Master Nbr], tbl02SongList.[Song Title], [Q1 Recording Dates and Location Match].[Recording Locations]

HAVING ((([Q1 Recording Dates and Location Match].[Recording Locations])=[Forms]![frmLocationSearch]![lstCitySearch]))

ORDER BY tbl01RecDatesList.[Recording Dates], tbl03SongMasterNbr.[Song Title Master Nbr];

Combo Box info...

Row Source: SELECT [tbl04 Recording Locations List].[Recording Location Prime Key], [tbl04 Recording Locations List].[Recording Locations] FROM [tbl04 Recording Locations List] ORDER BY [Recording Locations];

Bound Column: 1

John... Thanks for your help on this

Answer 3

What is the Row Source of your combo box, and what is the Bound Column?  And what is the SQL of the query that has the form parameter?  (Open the query in Design View, then switch to SQL view and copy and paste your answer.)

John Viescas, author

Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/

(Paris, France)

Answer 4

Thanks for the help John... but I have tried this and I'm unable to get this to work. I named everything just as you have but when I run the query I get a nothing in the results. I did it first with my own naming setup and nothing I then renamed everything as you have and still nothing any thoughts on what I'm doing wrong? Thanks for your continued help.

Answer 5

There's nothing you can do to control the prompt dialog box for a query.  But you can point a query parameter to a form, and that form can have a combo box or list box in which the user selects the parameter value.

Let's say you build a form called frmMusicSearch.  On the form, include a Combo Box that lists all the available cities.  Let's call the Combo Box cmbCitySelect.  Also include a Command Button on the form.  In the query you want to open (it's actually better if you use the query in a form or report), add a Criteria under the City field:

[Forms]![frmMusicSearch]![cmbCitySelect]

Looks kinda funky, but that tells the query to go find the form called frmMusicSearch (the form has to be open) and then use the value found in cmbCitySelect to filter the result.

Now in the Click event of your Command button, open the query or the form or report bound to your query using OpenQuery, OpenForm, or OpenReport.  Switch to Form view, pick a city, then click the button.  Magic.

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)