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]

Stoic:

You have me somewhat confused here:

You start with:


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:

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

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

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,cboPr ogram, 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