Filtering a subform based on interactive dropdowns

[kara507]

I would appreciate assistance in pointing me in the right direction. I've found some post that are very somewhat close to my challenge but not quite.

I have 3 interactive dropdowns (I'd prefer to keep them as interactive dropdowns and not cascading dropdowns). I do not need help with the interactive dropdowns, but they do show up as unbound in design view. The dropdowns are limited to values in table1. Depending on the selection of the interactive drop downs, I would like my subform to display results based on the selection of the dropdowns. I tried the parent/child approach but that didn't work since the dropdowns are showing up as unbound. I've come to the conclusion I need to do more filtering at the AfterUpdate event for all of the dropdowns, but not sure how to apply that to a subform.

I've included an image because I feel like reading descriptions of tables can be difficult to conceptualize sometimes. Thank you!

[NeoPa]

I'm having a deal of trouble trying to understand where you're coming from.

Can you explain why it wouldn't be as simple as filtering the subform based on the selected values in your ComboBoxes? The _AfterUpdate() code would build up the filter string based on any & all values present but ignore items with no value set. Is that not what you're after? If not then I'm thoroughly confused.

[kara507]

I think you understand correctly!

My challenge is that I haven't been able to find examples that would hint at how to approach it with multiple criteria (the dropdowns) and how to apply the criteria to table2. Most of the examples I've seen online are just a little bit too different for me to feel confident that it is the right approach. Setting up the interactive dropdowns was ok once I found parallel code, but the two reference textbooks I'm using (Access Bible and Microsoft Access 2019 Programming by Example) isn't quite sufficient to apply the filtering to the subforms.

For the filtering, I'm assuming I'd build a string that would retrieve the corresponding source ID based on dropdown criteria, and then have the subform spit out the available test results from table2 based on the source ID identified? I think I'm just stumped on how to bring the subform into the filtering since many examples use the parent/child approach.

Thank you!

[isladogs]

I'm still unclear what an interactive dropdown is other than a standard combo box. Am I missing something?

I think you just want examples showing how to apply multiple filters to your form/subform. If I'm correct then I have several examples on my website that may be helpful to you. Try a Google search for 'Multiple Group & Filter - Mendip Data Systems'

[kara507]

Sorry for the confusion. I may be using the wrong terminology here.

I've set up the drop downs so that if you select a State, the only available for the other dropdowns are ones that have records associated with that state. The dropdowns do not have be used in a specific order. If I understand correctly, cascading forces the user to select in a certain order and that wasn't something I quite wanted in my project. I wanted the user to be able to use the dropdowns in any order and still have the subsequent dropdowns influenced by the selection in other dropdowns. For example, based on the image provided above, there would be no way for a user to select State = PA and Type = Pond because no such entry exist in table1.

The non-cascading (interactive?) dropdowns may be irrelevant here. I only specified it since some examples I've seen use strictly the last dropdown in the cascade series to filter the subform which isn't quite what I wanted.

I'll do some google searching with the terms you suggested. Thank you!

[isladogs]

Sorry but that sounds like you are using cascading combos as the second combo values depend on the value selected in the first combo. The webpage I suggested earlier was for separate combos being used to filter different fields in the same dataset.

I also have a cascading combos example with 5 related combos. Forum rules prevent me providing a link but you can find that with a search for 'cascading combo boxes Mendip data systems'

[kara507]

I certainly won't argue if you say it is cascading combo boxes, you definitely know more than me! :)

In my case, I'm looking for examples to review on how to filter the subform (table2) based on the combo box selection (table1). I thought your first example that you provided might help me out a little bit, but I'm not sure that the dropdowns and the detail results are separate tables? It looks like your dropdowns and resulting tables are both from the PupilData table.

I'll take a look at the second example you recommended. Thank you!

[NeoPa]

The concept should be relatively straightforward if you consider it step-by-step.

First of all you want a process to run whenever any of your ComboBoxes are updated. You may also want the same process to run when you open the form originally. Thus we would consider a Procedure which is called from all those places.

The process would build a filter string based on all of the ComboBoxes, being flexible enough to ensure any empty ComboBox is excluded from the filter.

At the end of the Procedure the filter string needs to be applied and the Form (Of whatever type - SubForm; Pop-Up; Current Form; whatever.) Requeried.

To illustrate I've knocked up some boilerplate code. The names will likely differ from yours and the precise circumstances may need to be adjusted but it should give you the basic concept :

Private Sub DoFilter(frmVar As Form)
    Dim strWhere As String
 
    With Me
        If Not IsNull(.cboNum) Then
            strWhere = strWhere & " AND ([NumField]=%N)"
            strWhere = Replace(strWhere, "%N", .cboNum)
        End If
        If Not IsNull(.cboDate) Then
            strWhere = strWhere & " AND ([DateField]=#%D#)"
            strWhere = Replace(strWhere, "%D", Format(.cboDate, "yyyy\-m\-d"))
        End If
        If Not IsNull(.cboString) Then
            strWhere = strWhere & " AND ([StringField]='%S')"
            strWhere = Replace(strWhere, "%S", .cboString)
        End If
        If strWhere > "" Then strWhere = Mid(strWhere, 6)
    End With
    With frmVar
        If .Filter <> strWhere Then
            .Filter = strWhere
            .FilterOn = (strWhere > "")
        End If
    End With
End Sub

[kara507]

Hi NeoPa!

I totally follow your suggestion of building a procedure that runs at the afterupdate event of the combo boxes. This procedure would create the string of the “where” SQL conditions. Do you have any tips on how to apply this to the subform?

Under property sheet, my subform has a source object of table2. I do not see any filtering options on the property sheet except “Filter on Empty Master.” I am wondering if I need to create a new table2 and somehow integrate the resulting filter string into the and reference that dynamic table as the source object instead of table2?

Thank you for writing!


@NeoPa

[NeoPa]

I'm not sure I understand where your confusion is coming from. The procedure I showed as an example has the Form passed to it and every Form has the .Filter & .FilterOn properties so the example code already does that for you.

[kara507]

My apologies!!! I totally missed the top where you specify that you were passing a form into the subroutine. I've only worked with subroutines where you pass a maybe a script into it, but never a form. I had no idea you were could pass a form into a subroutine!!! I'll try it out and see if I can get it work! Thank you!

Private Sub DoFilter(frmVar As Form)

@NeoPa

[NeoPa]

We always have to bear in mind that a forum, by its very nature (Global; internet; etc.), is home to people of all levels of intelligence; experience; nationality; etc. Often it's unclear where the limitations are from but it generally doesn't matter. It's perfectly understandable not to understand everything at first glance if it's something that's new to you. There's a lot to pick up.

In all honesty there are so many possible ways to approach this. The Form needn't be passed as a parameter if it's always the same one and the code has access to it easily already. Passing it as a parameter in this instance was to ensure the name was clearly associated with the Form so any code referencing frmVar in the body was more easily understood. You could take the same approach with the various ComboBoxes too if you preferred.

[kara507]

I did not want to start a new topic if it wasn't necessary.

I spent more time with the Multiple Group Filter this week. Thank you for making this available for review. I think I'm connecting the dots a little more. May I ask how what design control (ex: subform, textbox, etc) is being used for pupil data? I thought it would simply be a subform, but it looks different than most examples I've seen with a subform.

Thank you!

[NeoPa]

I'm afraid you've completely thrown me with this one. You seem to be asking me about the design you have in your database, or in your head, I'm not sure. Why would you ask me that? Nothing in what I posted limits your options in that respect as far as I know.

I'd like to be able to help further but I can't see how at this point.

[kara507]

NeoPa!

First, thank you so much for your assistance earlier! I learned so much!

I apologize for the confusion, I thought I hit reply directly under one of isladogs post. This person encouraged me to google search Mendip Multiple Group & Filter. Google searching these terms took me to an example access file to view. My general (perhaps embarrassing question) was asking if the table under the filter options was a subform? Under design view, it doesn't quite look like a subform set up. I didn't want to start a new post to ask this question.

I don't know who you are, but I'm very grateful that you took the time to respond.

Edit - I think I just figured it out! It took me a while to find it, but it isn't a subform like I expected. The property sheet just says the Selection Type: Form, Continuous Form. I'll have to see if I can figure out how to add that. So much to learn!

Thank you!

[NeoPa]

Hi Kara.

Your explanation now makes perfect sense & puts everything into perspective that I couldn't see before :-)

Kara507:
I don't know who you are, but I'm very grateful that you took the time to respond.

While it is by no means expected for members to share who they are, I have no problems with it. If you click on my NeoPa Profile link you can see a bit about me. I'm Adrian Bell and I'm a Microsoft MVP for Access.

[isladogs]

Hi Kara
There isn't a subform.
My example app that you referred to is just a continuous form with textboxes for each field in the detail section.
However the header section has a series of command buttons instead of labels for each field header. Clicking on any of those buttons allows you to sort by that field. Clicking again reverses the sort...

BTW 2 fields have aliases which include a '?'. There are no special characters in the field names

Happy to answer other general questions as part of this thread. However, if you have more detailed questions regarding my app, it may be better to PM me as others won't be necessarily have the example app available to view