Pull down menu for query (1 Viewer)

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.

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.

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

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!

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

Click to expand...

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.

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.