Filter query based on comboboxes

[number1pita]

Hello,

I have the following options in a query:

Year
Category Type
Category ID
Office

I would like to have four combo boxes on a form where the end user can choose the year and click to export the query to Excel showing all fields with the selected year. I would also like the ability where if the end user wants to select a specific year and a specific category type they can choose their options and export the query to Excel showing a report of only that specific category type in that specific year they chose.

I was able to get the first combobox to work, but when I created the second combobox and I try to click on the drop down, there are no options that populate.

Any help would be greatly appreciated!

 

[Micron]

I was able to get the first combobox to work

To me, this means you got the values you want in the combo list, not that you got any query to partially work.
The form's recordsource needs to be set to the name of your query, and the row source for each combo you want to get field values from this query needs to be set to the appropriate field.
Since you said you have 4 combos but only provided info re: 2 of them, I'll stop there because I don't know if the other two are also going to be query criteria for your export. It would be more useful to handle the query statement in code if there are going to be more than two, else I might suggest using the second one as a filter.

 

[number1pita]

To me, this means you got the values you want in the combo list, not that you got any query to partially work.
The form's recordsource needs to be set to the name of your query, and the row source for each combo you want to get field values from this query needs to be set to the appropriate field.
Since you said you have 4 combos but only provided info re: 2 of them, I'll stop there because I don't know if the other two are also going to be query criteria for your export. It would be more useful to handle the query statement in code if there are going to be more than two, else I might suggest using the second one as a filter.

Ideally I would like for the end user to pick a year in a combo box (is there a way for them to select multiple if needed? That would be even better) and then yes they would then select the other criteria they want generated in the other combo boxes which would have a row source of the query QrySearchCMV (I had already set that up correctly) which would also be the record source for the form that I didn't know I had to do. Should I put all of the fields from the query down for the record source?

Any help would be greatly appreciated. I have a 10:00 am CST deadline for this tomorrow morning, and I have been hitting brick walls.

 

[Joe4]

The way I typically do this is have a Form, which combo boxes, check boxes, etc where the user makes all the selection they want.
I then have VBA code behind that form that builds the SQL code of the query, and then assigned that SQL code to an existing query (and then opens that query).

I am on my way out the door right know, but I have posted examples of how to do that in the past here. If you search these forums, you might be able to locate some of those threads. If you cannot find them and want to see them, I can look for them tonight, if you let me know.

 

[Micron]

As mentioned, the rowsource for your control(s) is a query or table field name from the query or table behind the report (unless these controls are to be unbound). If you want people to be able to put their own info in a control, it has to be unbound. Either way, you can pass these values to code that builds a sql string, or you can build a query that uses values from these controls as criteria by entering something like Forms!frmFormName.ControlName in the criteria row. This is easier for you and maybe others to maintain or alter, but Joe4's way of doing it is very common too.

No you cannot multi-select a combobox, but you can do so for a listbox. You do need code to get multiple values from a listbox if you want to use them in a sql construct. If you are dealing with dates, consider two textboxes that hold a start and end date and allow the user to enter one or both. The trick though, is to enforce a start date if there is an end date, plus ensure the end is greater than the start, plus figure out if they are even there at all. Don't mean to be negative, but I'm thinking you will not make your deadline unless you can find code snippets suggested by Joe4 or elsewhere, or simplifiy this to its least degree of difficulty and present what can work in such a short deadline. You could always say you have ideas on how to make it better, but it will take more time.

 

[number1pita]

Hi Joe4, yes if you could share the examples I'd appreciate it, I have been looking, searched by "Joe4" and by combo box/combobox, and I am just not finding it, so much to fish through. I was going to try and attach a copy of the database since it is all just test data at this point and nothing confidential, but I wasn't sure how to do that. That way it would give a better idea of what I have going on.

Micron, I had built the query and had the combobox pointing to that field in rowsource, it was year and I grouped it, and that works great. Then I go back to the query and in the year field I point to that combo box in criteria. It was working lovely and then I put a second combo box on the form, and it was blank when I did the same thing but to the category type field in query in row source of the combo box, and went into the same query and for the category type field I pointed to the second combo box in criteria. It didn't work. Blank. Then for some reason my first combo box started acting up a bit too, so to test things I deleted the first combo box (year) and went through all the steps again for category type combo box and it worked, but when I went to try and add in the year combo box again, didn't work. I'm missing something and I just can't quite figure out what it is.

Thank you to both of you for helping me though, greatly appreciated. If I don't have it done by my 10:00 CST deadline, it will have to be done at some point, so any help I can get I greatly appreciate!

 

[number1pita]

Oh, and I have actually never used list boxes before, kind of tried to figure it out, and gave up on it basically because I had used combo boxes and felt more comfortable with it, but if they would be a better option for what I am trying to accomplish I'd like to try it out.

Thanks!

 

[Joe4]

I did one recently on another forum. Here is is: vba code with query
It doesn't really matte whether it is done with Text Boxes or Combo boxes, the logic is the same.

 

[number1pita]

Awesome thanks Joe4, I'll try and get this to work for me and I'll let you know what happens, appreciate the responses!

 

[Micron]

for the category type field I pointed to the second combo box in criteria. It didn't work. Blank.

I suspect you put the second combo reference in the criteria grid on the same line. In that case, you'd be saying "Where date = something AND category = something. If both of these are not populated when the query runs, it won't work as you want. That's why I said you will require code that builds the sql string and/or uses secondary control values as a filter.
This forum doesn't allow uploaded files. You would have to use a file sharing website and share the link in this forum.
After this post, I likely will not be available for a few days.