Filter Row Source by Multiple Dropdown boxes (1 Viewer)

Mierin · Aug 11, 2017

Hello all,

I am not even sure if I can do what I am trying to do, but it feels like it should be possible. I have a form that is showing 3 pieces of equipment and their attributes. The source of the data is from a query that links together a dozen different tables. The form results filter by any combination of drop down selections to allow drill down to a specific configuration based on any/all parameters.

Is there a way to use the same filter string to update the row source for each dropdown so that it only shows the options that are left after filtering? I am using unbound dropdown boxes as selectors that are pointing to the same query as the form for their RowSource. It seems like I should be able to apply the filter to the RowSource before Requerying all of the dropdowns.

I think I just need to call the select query with a WHERE strFilter, but don't know how to make it work. I have looked, but do not see anyone trying to what I am trying to do in other questions. So, before I spend any more time chasing this feature, I wanted to check if I was even on the right page. I suspect I am missing something obvious that would solve my problem, hence why no one is asking about it. I have looked at the cascading combo boxes, but since I need someone to be able to select any/all of the fields and allow it to drill down, it does not seem to be my answer. Since I found how to make the form do it, I feel like I should be able to make the RowSource do it too.

Any thoughts on what I am trying to do would be most appreciated. I would post the db here, but I am contractually bound not to by my employer. Hence the theoretical question as opposed to a specific code question.

Thank you in advance for any all information anyone may be able to provide. I am using an accdb in Access 2013.

Ranman256 · Aug 11, 2017

build the 'where' clause by cycling thru all the controls....
it executes after every combo box AFTERUPDATE event

Code:

sub cboST_afterupdate()
FilterData
end sub

sub cboCity_afterupdate()
FilterData
end sub

sub FilterData()
dim sWhere as strin 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Mierin · Aug 11, 2017

Thank you, Ranman256. That is similar to how I currently have the form filter written, but I cannot see how would I get that to apply to the RowSource queries and not the form in general. It certainly isn't applying to my RowSources now, though it does filter my form correctly.

Shortened version of Current Code:

Code:

Sub AppFiltAll()
Dim strFilter As String

strFilter = ""

If Me.FRegion & vbNullStr <> vbNullStr Then
    strFilter = strFilter & " AND Region = '" & Me.FRegion & "'"
End If
If Me.FSysCombo & vbNullStr <> vbNullStr Then
    strFilter = strFilter & " AND [Model Combo Name] = '" & Me.FSysCombo & "'"
End If
If Me.FType & vbNullStr <> vbNullStr Then
    strFilter = strFilter & " AND [System Specs].Type = " & Me.FType
End If
 
If strFilter <> "" Then
    ' trim off leading "AND"
    Me.Filter = Mid(strFilter, 6)
    Me.FilterOn = True
Else
    Me.Filter = ""
    Me.FilterOn = False
End If
 
End Sub

How do I make my RowSource queries see that filter? Or, do I need to point my RowSource to something other than the base query being used by the form?

jdraw · Aug 11, 2017

You could build an sql string with the appropriate WHERE clause (criteria) within your form and based on the combobox selections that were made. Once all comboboxes have been updated or ignored you would then complete your SQL statement and use your newly created SQL with WHERE clause as new recordsource, and requery the form.

Mierin · Aug 11, 2017

Thanks, jdraw. I think we are closing in on it. That is exactly what I am not understanding. How do I make the RowSource use the WHERE clause? If I use WHERE strFilter, it pops up a box looking for a strFilter value. Are you saying I would populate a non-visible field on the form with the built string and then call it in the SQL? Something like:

Code:

SELECT DISTINCT [System Price Query].RegionID, [System Price Query].Region
FROM [System Price Query]
WHERE [Forms].[MyForm].txtFilter;

Is so, what would I use as the default value for that field since I assume the WHERE needs something that is not null to begin with on form load.

jdraw · Aug 11, 2017

If you are talking about the Form as an entity, then the Form's recordsource would be changed to reflect your selections, and then the Form reQueried.
If you are talking about a specific combobox, then you are dealing with a Rowsource.

I don't mean to be picky ---just making sure we are talking about the same thing.

Mierin · Aug 11, 2017

Sorry if I have not been clear. I have a filter on the whole form that is working. It takes the selections from the dropdowns and filters the form results accordingly. (That is what the code I posted in response to Ranman256 is.)

My question is regarding RowSource. I would like to make the options in each dropdown box filter like the form does (to limit the possible hundreds of options based on the other responses). I do not understand how to filter the RowSource queries (either using the already built Form Filter string or building a separate RowSource string to use in the WHERE clauses of all of the RowSource queries). Since people need to be able to select any/all of 20 different comboboxes, it isn't just a simple cascading combobox situation.

I can build the WHERE statement in VBA. I just don't understand how to get my RowSource query SQL statements to see and use the WHERE statement I built in VBA.

I think this is probably really simple. I just don't know how to do it. Part of the fun of being self-taught is occasionally missing something fundamental that no one asks questions about (because it is basic), so I couldn't just find the answer by googling. :banghead:

jdraw · Aug 11, 2017

Hmmm.

Are you asking if I select a value from Combo1/dropdown1, then only certain values will appear in combo2/dropdown2?
If so, then the concept is known as Cascading combos. here are 2 older videos from Datapig that illustrate the concept. These are a little older, but the concept is the same and the technique is still used.
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

If not, then please give an example to clarify.

I have a feeling that an example from your data would be most useful to readers.

Mierin · Aug 11, 2017

Thank you, but that doesn't actually answer my question at all. I need to be able to select or not select any/all of the comboboxes. If I use this method, then none of the comboboxes populate until selections are made in all comboboxes. I tried it. I even added in columns in the query builder to add an Is Null option for each field and it still shows nothing until selections in all fields are made. Which of course does not work since all fields are blank until selections are made in all fields. Catch-22. Hence, why I was asking my specific questions.

The comboboxes do not cascade (uni-directional), they simply apply filters (omni-directional). I know I should be able to call the WHERE statement I am writing in the SQL for each RowSource query. I just do not know how to make SQL see the string. Can anyone help?

jdraw · Aug 11, 2017

OK Let' get a real example. Post a copy of the evolving database with some sample data in ZIP format.
Provide instructions to get to the problem area.

Mierin · Aug 11, 2017

Sadly, I cannot post the database. As mentioned in my original post, it is contractually prohibited. Can you just write a sample of how to call a string written in VBA into a SQL query?

jdraw · Aug 11, 2017

It isn't so much the real data, it's your comboboxes and the query you're trying to create.
You can use Porky Pig and Donald Duck.

Here is a sample using vba to create SQL

Code:

Private Sub Combo0_AfterUpdate()
10    Debug.Print "Combo0-Net  " & Me.Combo0.value
20    Me.Combo2.RowSource = "SELECT A.SwitchId,A.Switch from tblSwitchA A,tblNetworkedSwitch N" _
       & " where A.SwitchId = N.SwitchId " _
       & " and N.NetworkId = " & Me.Combo0.value & ";"
       
      'Clear combo2 in case we have been testing/playing
30    Me.Combo2.value = Null

and another

Code:

Private Sub Combo2_AfterUpdate()

10    Me.CboPort.value = Null
20    Me.CboPort.RowSource = "SELECT PortId, iif(Active = 0,'Yes','No') as Actv from tblPort" _
       & " Where switchId =  " & Me.Combo2.value & ";"

30    MsgBox "After selecting Combo0 has " & Me.Combo0.ListCount

End Sub

You may get more info/insight from this material by Martin Green.

Mierin · Aug 14, 2017

AH HA! I write the entire SQL query in VBA and assign to the RowSource! Thanks for sticking with me, jdraw. That is exactly the switch I needed flipped in my brain. The support article is most appreciated for syntax reference. I really need those cancan lady emojis to express my joy...

Seriously, I had spent days (on and off) looking for answers to that. Many thanks.

Filter Row Source by Multiple Dropdown boxes (1 Viewer)

Mierin

New member

Ranman256

Well-known member

Mierin

New member

jdraw

Super Moderator

Mierin

New member

jdraw

Super Moderator

Mierin

New member

jdraw

Super Moderator

Mierin

New member

jdraw

Super Moderator

Mierin

New member

jdraw

Super Moderator

Mierin

New member

Similar threads

Users who are viewing this thread