How do I query a Combo-box or Drop-down list values with their own seperate queries?

Question

I think that this should be a fairly straight-forward question and probably very easy for an experienced developer to answer.

For this instance I'm building a Database of my t206 baseball collection.

There is an infinite number of queries that I will be making. However adding them to a form with a buttons will be very ugly. I don't want to have 100 buttons on a form.

I thought of something rather simple. A Drop-down or a Combo-box. I would like to add my own list values to either and depending on what is selected from that list it will run a query associated with the list item.

For example I have queries set up for all "American League" cards, American League cards owned, and American League cards needed.

I would simple like to add those three list items to either a Drop-down or Combo-box whichever is the preferred method and easiest to achieve.

Answer 1

Hi,

I could be wrong but I think the "easiest" method of dynamically adding items to either or both lists is to store your items in a table and simply base your lists on that table. For example, create a table and populate it with the names of your queries. You can even add a description column and, if it might make it look better, display only the description but the code will use the query name column to open it.

Just my 2 cents...

Answer 2

I have the table created already. As well as multiple queries that query that table. I was simply trying to create a drop down list. When you select something it will query your selection based off of what you selected.

Answer 3

I have the table created already. As well as multiple queries that query that table. I was simply trying to create a drop down list. When you select something it will query your selection based off of what you selected.

Hi,

Sorry, I guess I am confused what your question really is. Is it how to create a list or how to open the query when an item from the list is selected?

The table I was referring to earlier is a table with a list of all your queries. For example:

QueryName     Desc
Query1        Show All American League Cards
Query2        Show All American League Cards Owned
Query3        Show All American League Cards Needed

So, when the first item is selected from the list, the code will open or run Query1.

Hope it helps...

Answer 4

I should have included a photo from the get go lol. I made a drop-down. Rather than having all of those buttons and assigning queries to them. I'd simply like to select my drop down item. Say the highlighted one below. And as you can see I have a query set up for all of those drop-down values.

When a drop-down item is selected. I'd like to run the query associated with it. Example the one selected, "Minor League Needed" would run the query in the object panel "qry_Minor League Needed"

Answer 5

Hi,

Thank you for the clarification. Assuming the dropdown has two columns (one of them being hidden) and it is bound to the column containing the name of the query, then you should be able to use pretty much the same code you're using for your buttons. For example, if you have something like this for your button:

DoCmd.OpenQuery "[qry_Minor League Needed]"

Try using this for the dropdown:

DoCmd.OpenQuery Me.DropDownNameHere

or maybe like this (because you have spaces in your query names), in case the first one doesn't work:

DoCmd.OpenQuery "[" & Me.DropDownNameHere & "]"

Hope it helps...

Answer 6

I'm sorry I'm sort of new to doing this stuff in Access. Where would I put that code? The control source?

Additionally that's only 1 query while I'll have several drop-down selections. Would I need to concat multiple statements together in the control source?

DoCmd.OpenQuery "[qry_Minor League]"

DoCmd.OpenQuery "[qry_Minor League Needed]"

DoCmd.OpenQuery "[qry_Minor League Owned]"

I tried adding both to the control source and that didn't work:

DoCmd.OpenQuery "[" & Me.DropDownNameHere & "]"

DoCmd.OpenQuery "[qry_Minor League Needed]"

Answer 7

Hi,

Where to put the code depends on when you want the query to open. For example, if you want the query to open as soon as you select an item from the list, then you can use the AfterUpdate event. Otherwise, you could add a button on the form labeled "Open Query" and use its Click event.

You only need one code, simply:

DoCmd.OpenQuery Me.DropdownName

The "Me.DropdownName" will be replaced by the name of the query to open automatically for you.

Hope it makes sense...

Answer 8

Sorry I won't bug you after this. Probably syntax issue:

Answer 9

Hi,

In the AfterUpdate event, select [Event Procedure], then click on the three dots next to it. Then, in the code window that opens up, enter the following exactly:

DoCmd.OpenQuery Me.Combo17

Hope it helps... (remember what I said about spaces in query names)

Answer 10

I suck sorry =/

Answer 11

Hi,

What happened to "Do" it's DoCmd, not just Cmd.

Answer 12

I fixe the code not sure what happened must not have copied it properly. I think that it's close to working though. Now when I select any of the drop-downs I'm getting a specific error to the selection.

Or to say I select "American League Needed" then it says "

The same thing will happen if I select "National League Needed". I'll get the same error but it will specify Microsoft Access can't find the object 'National League Needed'. So I think it's close to actually working lol.

Answer 13

I am wondering if it's because of the spaces. Did you try adding the square brackets like I mentioned earlier? Can you post your latest code version? Thanks.

Answer 14

So I did note your spaces and rather than using the brackets when you had said that above I simply changed the name of the queries to not include spaces in the name, thinking that would resolve a lot of issues. I will do the rest for my other queries (those aren't being used in this combobox at this time).

Answer 15

Hi,

Does it mean the code works now? If so, congratulations and good luck with your project.

Answer 16

That's still a negative. It's not working properly. I tried changing the query names by getting rid of the space in hopes that would satisfy the problem, but it didn't.

Answer 17

Oh, sorry to hear it. Are you able to share a copy of your db without data, so we can take a look? Not sure where to go next without knowing exactly what you have or did.

Answer 18

Yeah I can share a copy no problem. This is strictly for fun and it's just baseball cards haha. Not sure how I'd go about sharing the file however. I don't think you can share files on this site if I'm not mistaken.

Answer 19

Hi,

You can post your db on a file share and post a link for download here or you can email it to me, if you like.

Just a suggestion...

Answer 20

I'd prefer to email it to you if you don't mind.

Answer 21

I'd prefer to email it to you if you don't mind.

No problem. My email address is in my profile. Let me know if you don't find it. It's also available on my website.

Answer 22

Hmm, scratch that. I thought it's on my profile (why is it not?). Anyway, here it is: thedbguy[at]gmail.com