Use a single control to query more fields or combo boxes

[Stoic]

Hi,
I have a database with tables and fields. I have created a form that with three to four combo boxes and inserted a control button.
I would like a write a code to query my data based on the selection(s) made from the combo boxes when I click the command button.

I have this code and it is working perfectly well, only that I have created multiple command button to perform the queries I need.

Here is what I have:

Dim Filter As String
Dim strProgram As String, strCohort As String, strCounty As String
 
 If Me![cboSProgram] = "Select Program" Then Exit Sub
 If IsNull(Me![cboSProgram]) Then Exit Sub
 If Me![cboSCohort] = "Select Cohort" Then Exit Sub
 If IsNull(Me![cboSCohort]) Then Exit Sub
 If Me![cboSCounty] = "Select County" Then Exit Sub
 If IsNull(Me![cboSCounty]) Then Exit Sub
 
     strProgram = Nz(Me.cboSProgram, "*")
     strCohort = Nz(Me.cboSCohort, "*")
     strCounty = Nz(Me.cboSCounty, "*")
     Filter = "strProgram = """ & cboSProgram & """ And strCohort = """ & cboSCohort & """ And strCounty = """ & cboSCounty & """"
     DoCmd.OpenForm "tblSortSchools", acFormDS, , Filter
 
     cboSProgram = "Select Program"
     cboSCohort = "Select Cohort"
     cboSCounty = "Select County"
     cboSCohort.Requery
     cboSCounty.Requery
 

In my code, I have two combo boxes but before the control works, I must make both selections. What code can I include that if I make only one selection and click the command button, I still get the query of my selection.

Any idea will be useful.

[zmbd]

Basically, get rid of all but one of your command buttons.
In the remaining button, you will check for either a selection in the dropdown list and include it or ignore it and then build the string.

Have you taken a look thru the following:

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

These cover the vast majority of filtering and lookup type form concepts.

[Stoic]

Thanks zmbd,
I don't seem to catch the option. Can you explain further. My mind is, using one control button that functions/queries multiple selections.

[zmbd]

Stoic:

You have me somewhat confused here:

You start with:

that with three to four combo boxes and inserted a control button.
(...)
this code and it is working perfectly well
(...)
only that I have created multiple command button to perform the queries I need.

Ok, comnbine the code fromt he multiple command buttons to review the selections within the comboboxes and build the string to filter.

Then you finish with:

(...)
In my code, I have two combo boxes but before the control works, I must make both selections. What code can I include that if I make only one selection and click the command button, I still get the query of my selection.

Now this is either a second question or an attempt at clarifiyng your goal from the first part of the message which is what I intrepeted this to be hence the links I provided.
These links will take you thru most of what you need to set up filters.

Now you have

using one control button that functions/queries multiple selections.

So are you still looking at multiple comboboxes or have you changed your mind and are now looking at a single combobox with mutliple selections within its dropdown?

-z

[Stoic]

@zmbd
I am sorry if I confused you, my first question stands. I was only trying to clarify what I meant. Yes, I have the combo boxes label as cboCounty,cboProgram, cboProgram ... each of these combo has a dropdown for selection. I have placed a control button to query base on what I select from the combos. that is working perfectly if I make selection from all three combo, but if I make selection from only one combo box, the button does nothing. So I would like even if I make only one or two selections and leave the other combo empty, and run the command, I should still get my query result.
I hope I am clear on this.
Thanks