Baby Steps - Multiple Combo Boxes filtering a form

[christine407]

Hello, new to VBA. I apologize for starting what seems like an infinitely duplicate thread, but the forum rules seem to indicate this is ok. I have the standard challenge of filtering a form based on multiple combo boxes. I’ve watched a few tutorials and I’ve managed to get myself a little confused. Eventually, I have to hyperlink the unique ID of the form to go to another form, but baby steps and baby steps for me. I’ve successfully set up the form part. Took me a while to find the right video to set up the tabular/continuous table set. I am placing my combo boxes in the header portion of the form.

I have three general questions:

(1) When using the combo box wizard, do I want to select I want the combo box to get the values from another table or Query or find a record on my form based on the value I selected in my combo box? I normally use the first option but I’m seeing tutorials that use the second option as well.

(2) This thread (titled multiple combo box filters in a form posted by Tskutnik in Nov 2020) caught my eye. I had planned on building a filter string based on the selection within the combo boxes, but the last two post indicates it might not be the best idea if you are working with a table that comes from ODBC. In my case, my table is obtained through ODBC connections. If building a filtering SQL string is not a good idea, can someone provide a little more context for why this isn’t a good idea?

(3) For the filtering, I’ve seeing different approaches for having the multiple combo boxes. For the time being, I’ve settled on the following general approach below (although I could be swayed to do it differently). My question is how to I apply the filter string to the form, is it under row source? The original example I was reviewing used this approach to apply the filter string to subsequent combo boxes so that the user did not have to use the combo boxes in a strict cascading order. So if the user selects a country, the available cities in the next combo box correspond to that country. Here is my general intended approach for the interactive combo boxes. I just don’t quite follow where to apply the filter (or if I need to make a separate one) for the form. Thank you!

cboABC_AfterUpdate()
BuildFiltStr
End Sub
--
Private Sub cboABC_GotFocus()
Me.AllowEdits = True
If Nz(FiltStr, "") = "" Then
Me.FiltABC.RowSource = "SELECT DISTINCT TABLE.ABC_NAME FROM TABLE; "
Else
Me.FiltABC.RowSource = "SELECT DISTINCT TABLE.ABC_NAME FROM TABLE WHERE " & FiltStr & ";"
End If
End Sub
--

Sub BuildFiltStr()
'Make where condition blank at the beginning of the routine
FiltStr = ""
'If a field has been selected, change the filter string to include the field condition
If Me!FiltABC <> "" Then
FiltStr = "[BOTM_FLD_NAME_CD] = '" & Me!FiltABC & "'"
End If
'If no conditions have been set, then change make filter false
If FiltStr = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = FiltStr
Me.FilterOn = True
End If
End Sub

 

[pbaldy]

1) Those are apples and oranges. I wonder if you mean the options to get values from a table/query vs a value list? If so, I virtually always use a table to store values.

2) I would start out using your filtering string and reevaluate if the performance with an ODBC table wasn't good. Access is usually pretty good at making the server do the work.

3) If you're building a filter, you'd set the form's Filter and FilterOn properties. An option is changing the RecordSource property, which would require a complete SQL string rather than just a filter clause.

 

[Uncle Gizmo]

I have a ready-made product which does essentially what you want, however it doesn't use combo boxes, it uses text boxes. You can see the product on my website here:-

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...

www.niftyaccess.com

You are welcome to a free copy of the product, just contact me for details on how to obtain a free copy.

I have considered making a version which uses comboboxes, but I think it would actually reduce it's functionality.

 

[christine407]

1) Those are apples and oranges. I wonder if you mean the options to get values from a table/query vs a value list? If so, I virtually always use a table to store values.

2) I would start out using your filtering string and reevaluate if the performance with an ODBC table wasn't good. Access is usually pretty good at making the server do the work.

3) If you're building a filter, you'd set the form's Filter and FilterOn properties. An option is changing the RecordSource property, which would require a complete SQL string rather than just a filter clause.

With respect to question 1. I want the options available on the combo boxes to be limited to the unique options available on the table. The example I reviewed online used option 2, but I think this person may have been implementing a different filter approach to implement on his form. I think what throws me off on I want the combo box to get the values from another Table or Query option is that it asked for where you want to "store" the information and I don't think I need to be storing any information? Option 2 did not seem to request information on where I wanted to store the selected value.

With respect to question 2, I will proceed with the filtering string and see how it goes! Thank you.

With respect to question 3, can you give very brief example of the difference between a filter clause vs a SQL? I guess I feel like the example I'm using above uses both?

Thank you!

 

[MajP]

Here are a couple of modules that will save you HOURS and HOURS and HOURS of work.
I knocked out a six combo filter with all combos cascading (only show what can be filtered) in this much new code. All the rest is reusable in the modules. The filterform and getfilter are the real part of the solution. This would normally take hundreds of lines of code to write.

Public Function FilterForm()
  Dim Criteria As String
  Dim qdf As QueryDef
  Criteria = GetFilter()
  Set qdf = GetDynamicQuery("qryData", "qryDynamicData", Criteria)
  Me.subFrmData.Form.RecordSource = qdf.Name
  Me.txtFilter = Criteria
End Function
Public Function RequeryCombo()
  ActiveControl.RowSource = ActiveControl.RowSource 'You cannot use a requery here. I have no idea why
End Function
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'************************************************************************  Get Filters From Controls  *********************************************************************************
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Private Function GetFilter()
  Dim FltrFirstName As String 'Need variable for each control used to filter
  Dim FltrLastName As String
  Dim FltrCity As String
  Dim FltrState As String
  Dim FltrHired As String
  Dim FltrYear As String
  Dim AndOr As CombineFilterType 'Only needed for demo of combining filter

  'use the controlFilters module
  FltrFirstName = GetFilterFromControl(Me.cmboFirstName) 'Creates a filter string
  FltrLastName = GetFilterFromControl(Me.cmboLastName)
  FltrCity = GetFilterFromControl(Me.cmboCity)
  FltrState = GetFilterFromControl(Me.cmboState)
  FltrHired = GetFilterFromControl(Me.cmboHired) ' Handles dates
  FltrYear = GetFilterFromControl(Me.cmboYear) 'Handles numeric
  Select Case Me.frameType
    Case 1
      AndOr = ct_Or
    Case 2
      AndOr = ct_and
   End Select
  
  'Combine all of them regardless if nothing is selected
  GetFilter = CombineFilters(AndOr, FltrFirstName, FltrLastName, FltrCity, FltrState, FltrHired, FltrYear)
  
End Function

Private Sub cmdClear_Click()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
   If ctrl.Tag = "Clear" Then
     ctrl.Value = Null
   End If
 Next ctrl
  FilterForm
   
End Sub

Private Sub Form_Load()
   'Reset the dynamic query
   Me.subFrmData.Form.RecordSource = "qryData"
   SetDynamicQuery "qryData", "qryDynamicData", "1=1"
End Sub

Private Sub frameType_AfterUpdate()
  FilterForm
End Sub

There is way more to these modules. They can pull filters from multi select listboxes, text boxes, between filters, etc.
I have this defaulted to an "Or" search which gives correct results, but is a little strange with the cascading effect. The cascade reduces the choices, but when set to OR it expands the results. More for demo purposes than usefulness.

 

[christine407]

Here are a couple of modules that will save you HOURS and HOURS and HOURS of work.
I knocked out a six combo filter with all combos cascading (only show what can be filtered) in this much new code. All the rest is reusable in the modules. The filterform and getfilter are the real part of the solution. This would normally take hundreds of lines of code to write.

Public Function FilterForm()
  Dim Criteria As String
  Dim qdf As QueryDef
  Criteria = GetFilter()
  Set qdf = GetDynamicQuery("qryData", "qryDynamicData", Criteria)
  Me.subFrmData.Form.RecordSource = qdf.Name
  Me.txtFilter = Criteria
End Function
Public Function RequeryCombo()
  ActiveControl.RowSource = ActiveControl.RowSource 'You cannot use a requery here. I have no idea why
End Function
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'************************************************************************  Get Filters From Controls  *********************************************************************************
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Private Function GetFilter()
  Dim FltrFirstName As String 'Need variable for each control used to filter
  Dim FltrLastName As String
  Dim FltrCity As String
  Dim FltrState As String
  Dim FltrHired As String
  Dim FltrYear As String
  Dim AndOr As CombineFilterType 'Only needed for demo of combining filter

  'use the controlFilters module
  FltrFirstName = GetFilterFromControl(Me.cmboFirstName) 'Creates a filter string
  FltrLastName = GetFilterFromControl(Me.cmboLastName)
  FltrCity = GetFilterFromControl(Me.cmboCity)
  FltrState = GetFilterFromControl(Me.cmboState)
  FltrHired = GetFilterFromControl(Me.cmboHired) ' Handles dates
  FltrYear = GetFilterFromControl(Me.cmboYear) 'Handles numeric
  Select Case Me.frameType
    Case 1
      AndOr = ct_Or
    Case 2
      AndOr = ct_and
   End Select
 
  'Combine all of them regardless if nothing is selected
  GetFilter = CombineFilters(AndOr, FltrFirstName, FltrLastName, FltrCity, FltrState, FltrHired, FltrYear)
 
End Function

Private Sub cmdClear_Click()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
   If ctrl.Tag = "Clear" Then
     ctrl.Value = Null
   End If
Next ctrl
  FilterForm
  
End Sub

Private Sub Form_Load()
   'Reset the dynamic query
   Me.subFrmData.Form.RecordSource = "qryData"
   SetDynamicQuery "qryData", "qryDynamicData", "1=1"
End Sub

Private Sub frameType_AfterUpdate()
  FilterForm
End Sub

There is way more to these modules. They can pull filters from multi select listboxes, text boxes, between filters, etc.
I have this defaulted to an "Or" search which gives correct results, but is a little strange with the cascading effect. The cascade reduces the choices, but when set to OR it expands the results. More for demo purposes than usefulness.

The And/Or function is super interesting! I love love that it builds the SQL string on the bottom. I will study the script closer in detail tomorrow.

Quick question, any reason why you chose to display the table within a subform rather than the general detail form? I started the project utilizing subforms as well, but then recently switched to continuous table form. I'm not opposed to switching back to subform, I'm not sure if there are limitations of working with a table (continuous dataset view) in a subform vs form. Thank you!

 

[MajP]

I was too lazy to build a tabular form. So easier to build a datasheet.

 

[MajP]

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

May be of interest

 

[MajP]

For demo purposes I added 3 forms.
1. Modifies the underlying query SQL
2. Applies a form filter
3. Applies filter to a listbox
The only real difference is how you use the filter the steps to get the filter are the same.

The first adds a level of complexity with the cascade and modifying the query, but if you follow the steps it is simple.

I will study the script closer in detail tomorrow.

I would not spend time trying to understand the modules WORK. I did some complex coding. What you want to do is understand how to USE the procedures.

 

[christine407]

For demo purposes I added 3 forms.
1. Modifies the underlying query SQL
2. Applies a form filter
3. Applies filter to a listbox
The only real difference is how you use the filter the steps to get the filter are the same.

The first adds a level of complexity with the cascade and modifying the query, but if you follow the steps it is simple.

I would not spend time trying to understand the modules WORK. I did some complex coding. What you want to do is understand how to USE the procedures.

Wowza! I'm working my way through Access 2019 Programming right now. You may be right that some of the vba language will still be above my head a little bit, but I'll definitely put in some time! This is so awesome!

I've found 3 different versions of the same exercise (6 combo boxes applied to a table) online, but they all take different approaches. It wasn't until I saw your ComboSearch_V3 file that it becomes more clear that there are indeed multiple ways to apply a filter! I took a VBA udemy course and I think I got attached to the idea of only one approach to apply a filter and got a little lost reviewing other examples. This file is really appreciated because you provided a way for the reviewer to see how one can obtain the same results with two different approaches. It can be confusing to compare the method of two different projects not knowing if the different approach is by choice or design.

Quick question if you have a moment, ideally, I'd want to export the results of table (after applying the combo box conditions) into excel. With my limited understanding, would you recommend the filter/Query approach or filter/Form approach? My sql background hinting at query, but I don't trust myself yet. Thank you!

 

[MajP]

I've found 3 different versions of the same exercise (6 combo boxes applied to a table) online, but they all take different approaches. It wasn't until I saw your ComboSearch_V3 file that it becomes more clear that there are indeed multiple ways to apply a filter! I took a VBA udemy course and I think I got attached to the idea of only one approach to apply a filter and got a little lost reviewing other examples. This file is really appreciated because you provided a way for the reviewer to see how one can obtain the same results with two different approaches. It can be confusing to compare the method of two different projects not knowing if the different approach is by choice or design.

The three approaches I did are really the same with slight modifications. There are several more approaches that are fundamentally different. One of the most popular is is to use control references in the query. I will almost never use this. The above demo with multiple comboxes would be a nightmare to debug. It is fine if you have one control. In a query you may see a criteria that has something like
forms![frmsearch]!cmboLastName or isnull(forms![frmSearch]![cmboLastName])
I would have to do the above for 6 controls, each ("![ would have to be perfect.

I will add a demo to that with one or two controls showing this method.

You may be right that some of the vba language will still be above my head a little bit, but I'll definitely put in some time! This is so awesome!

Do not worry about how the watch works, but how to use it. Good code is like a black box, where you just need to understand the inputs and outputs.

Quick question if you have a moment, ideally, I'd want to export the results of table (after applying the combo box conditions) into excel. With my limited understanding, would you recommend the filter/Query approach or filter/Form approach? My sql background hinting at query, but I don't trust myself yet. Thank you

This will do 90% of what you would want to do without any code. I put this in most of my DBs. Just drop the form and the ExportQuery into a db. I start all my queries with the letters "qry" so any query starting with this shows up in the pulldown. You can modify this if you need to. Once a query is loaded you can filter and sort using the drop down arrows. Hit export to excel. You can use this now without any other filters. It has a few limitations on the filtering, but many more advantages. You can do very complex filtering with checkboxes and dates.

 

[pbaldy]

With respect to question 3, can you give very brief example of the difference between a filter clause vs a SQL? I guess I feel like the example I'm using above uses both?

The filter is the SQL WHERE clause without the word "WHERE". So SQL might be:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = 7

the filter clause would simply be "Field1 = 7"

 

[christine407]

Closing out this thread with a few comments in case anyone reviews this!

1) I ended up using "I want the combo box to get the values from another table or query" and using a SELECT DISTINCT statement under properties>row source so that the combo box was limited to values existing in the table, I think you can also select unique values under the properties sheet as well.
2) No comments here, I ended up using sticking with the filter approach by building there WHERE SQL conditions based on selection in the combo boxes.
3) After finding other files to review, I realized that I never made my Me.FilterOn = True or False (depending if there was anything inputted for the FiltStr). Under properties, the Filter Lookup was set to Database setup. Once I added these statements, my form FINALLY filtered.

Thank you to everyone who helped! The work that MajP provided was awesome, but I struggled to use it correctly. Hopefully I understand it someday soon!

Happy to change flair to solved (if I can find out how to do it)

 

[pbaldy]

Glad you got it sorted out.