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

  • 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.

    • Edited by stillanoob Friday, July 13, 2018 2:28 PM
    Friday, July 13, 2018 12:39 PM

All replies

  • 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...

    Friday, July 13, 2018 2:46 PM
  • 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.
    Friday, July 13, 2018 3:03 PM
  • 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.


    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...

    Friday, July 13, 2018 3:17 PM
  • 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"

    • Edited by stillanoob Friday, July 13, 2018 3:50 PM
    Friday, July 13, 2018 3:49 PM
  • 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...

    Friday, July 13, 2018 4:00 PM
  • 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]"

    • Edited by stillanoob Friday, July 13, 2018 5:12 PM
    Friday, July 13, 2018 5:09 PM
  • 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...

    Friday, July 13, 2018 5:21 PM
  • Sorry I won't bug you after this. Probably syntax issue:

    Friday, July 13, 2018 5:34 PM
  • 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)

    Friday, July 13, 2018 5:56 PM
  • I suck sorry =/

    Friday, July 13, 2018 6:16 PM
  • Hi,

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

    Friday, July 13, 2018 6:26 PM
  • 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.

    Monday, July 16, 2018 10:48 AM
  • 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.
    Monday, July 16, 2018 3:03 PM
  • 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).

    Tuesday, July 17, 2018 11:46 AM
  • Hi,

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

    Tuesday, July 17, 2018 2:34 PM
  • 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.
    Tuesday, July 17, 2018 6:27 PM
  • 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.
    Tuesday, July 17, 2018 6:35 PM
  • 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.
    Wednesday, July 18, 2018 10:48 AM
  • 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...

    Wednesday, July 18, 2018 2:34 PM
  • I'd prefer to email it to you if you don't mind.
    Thursday, July 19, 2018 1:15 PM
  • 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.
    Thursday, July 19, 2018 3:09 PM
  • Hmm, scratch that. I thought it's on my profile (why is it not?). Anyway, here it is: thedbguy[at]gmail.com
    Thursday, July 19, 2018 3:10 PM