Pull down menu for query

[sweetmikie]

Hi, everyone! I need some help, please. My problem is: I have several fields which are combo boxes and I want to be able to do a query to search for each choice. In other words, I have choices A, B, C, D, and E for a combo box. Rather than typing out each one, I want to have a query where a pull-down box will appear, allow me to choose any of these 5 choices, and once chosen, have a "Go" button or some such thing to initiate the query action. Is this possible? Thank you for any help you may provide.

 

[MarkK]

Yes, of course this is possible! You need to build a SQL statement on the fly, based on what is selected--and what is not selected--in the comboboxes in question.

There are a couple of code samples I've posted over the last few days, one is here: http://www.access-programmers.co.uk/forums/showthread.php?t=289218 and another here: http://www.access-programmers.co.uk/forums/showthread.php?t=289217

These show one way of breaking the SQL statement into parts, and then assembling those parts as required based on the values in the controls on a form, thereby creating dynamic, responsive SQL statements.

 

[sweetmikie]

Thank you for your swift reply! I will give it a shot and let you know if I can get it working!

 

[sweetmikie]

OK, I read your posts and tried to follow the directions. First, I had to create a separate form which only shows the combo box. I then placed below it to initiate the query. However, when I select the value and hit the "Run Query" button, it does a query for the entire field, rather than for the exact parameter I wanted. So, the combo box has choices "A, B, C, D, E", and when I select "C" it will show me all of them. How can I get it to only select the value I choose? At least I am part of the way there. Thank you so much!

 

[MarkK]

Do you have code you wrote that isn't working? I don't know to troubleshoot your description of the failure . . .

it does a query for the entire field, rather than for the exact parameter I wanted

 

[sweetmikie]

Mark,

I haven't written any code for it. It is basically a database where I want to store the sheet music I have. I haven't entered the actual data, but just some dummy data to see how it works. I have a field called "genre", where I label the genre of music a piece has. I use a combo box for this, on my entry form. I want to design a query where I can search all of the music and find out how many "rock" or whatever type of pieces I have in my library. To do this, I designed a separate form from the entry form, showing only a pull-down menu listing the different genres of music, in order to select one type for the query. Then, under it, there is a "go" button to initiate the query action. When I select the type and hit the "go" button, it lists all types. I am nearly clueless, but have come a good ways since beginning the database (had a short Intro to Access class in college). I don't know VBA or SQL, and am frankly intimidated by it. I will attach my database to see if that will help you. I may have created many redundant things in this. I am so grateful for any help or advice you are able to provide. Thank you so much.

 

[MarkK]

You need to learn how to write queries, which are SQL, but in design view there is a grid you can use, and the actual SQL is written for you. That is the best way to count things, like how many rows have a genre of 6, let's say.