NOTE I significantly edited my original post to include more relevant information and removed insignificant information. Some of the comments are based on the information removed, but the integrity of the original question is still intact and the comments are still relevant and useful.
In my database I have a table tblStandards that has a list of compound names with their associated fields.
I created a form to generate a subform which only populates compound names and their data when you select a solution mixture (I called these "handles"). I am using this subform as a means of generating a "Location Finder" of all the respective compounds associated with that solution mixture "handle". The issue is some compounds are used in multiple different mixtures, thus having multiple "handles". I have included a cascading combo box as recommended by @June7, but I am running into an issue with the last combo box filter.
I don’t want to have a separate field attached to my tblStandards with the combo box display value ("handle") just to filter my table (which is what I have done in the past). Using combo box display value field in my table works but it is limited and I wish to make it more modular. I have a second table tblComboBox which has three fields; [Compound Name], [Standard_InternalStd], and [Cal_QC_Handle].
I have the cascading combo box working, except for last filter. I think I found a round-about way to filter based on my tables. I am using an After_Update event with the FilterOn function to filter a TempSubform which is filtering the tblComboBox based on matching the [Cal_QC_Handle] to the cboxSecondChoice, then using TempSubform![Compound Names] field as a second FilterOn function for my MainSubform.
My code is working, but the second FilterOn is only using the first Compound Name from my TempSubform to filter MainSubform. I want to filter based on all the rows in my Tempsubform.[Compound Name]. I think my issue is in my " & Me.Temsubform![Compound Name] &" block of code.
Private Sub cboxSecondChoice_AfterUpdate()
Dim Filter_Tempsubform As String
Filter_Tempsubform = "[Cal_QC_Handle] ='" & Me.cboxSecondChoice & "'"
Forms![Standards_Form]![TempSubform].Form.FilterOn = False
Forms![Standards_Form]![TempSubform].Form.Filter = Filter_Tempsubform
Forms![Standards_Form]![TempSubform].Form.FilterOn = True
Dim Filter_MainSubform As String
Filter_MainSubform = "[Compound Name] = '" & Me.TempSubform![Compound Name] & "'"
Forms![Standards_Form]![MainSubform].Form.FilterOn = False
Forms![Standards_Form]![MainSubform].Form.Filter = Filter_MainSubform
Forms![Standards_Form]![MainSubform].Form.FilterOn = True
End Sub
The issue is the filter is only using one Compound Name (the first on the list) to filter. I need the filter to use all the Compound Names from the newly filtered TempSubform. Apologies if this is a very convoluted approach. Every example that I have seen so far always has the combo box value embedded in the table that they are filtering, which is something I wish to avoid.
UPDATE
I have abandoned my 1st attempt (the double FilterOn function with a temporary Subform) and I have a working form! I had to modify my tblStandards to include a new field which contained values from my final combobox selection (which I wanted to circumvent initially due to my perceived limitations of this design). The new Form-Handle field on my table is a string with multiple single "handles" which I use if the final combobox selection (only a single "handle" can be selected) is in the subform using the wildcard "Like *". Each one of these "handles" corresponds to a solution mixture which is a specific list of compounds.
This solution allows me to have a single compound used to make multiple different solutions, and if you select any of these solutions to make from the dropdown combo box the compound will be returned. I think this is the simplest solution to my issue and I might have just been too stubborn looking for a over-complicated solution.
I modified code from Allen Browne's ms-Access "Filter a Form on a Field in a Subform"
As @June7 said, cascading ComboBoxes (or ListBoxes) are the standard UI design for multistage filtering of data tables. If I have the available form real estate, I prefer cascading ListBoxes over ComboBoxes, because of the visual cues they provide. For example, look at how Digi-Key does it. Problems with cascading ComboBoxes have been questioned to death here. Boxes which filter data, list or combo, are usually unbound, and should not be simultaneously used to update data. Their sole purpose is to display item choices, select an item, and pass it's value to the next filter.
I'm linking below to a few tutorials, which require little to no VBA code. The 3rd tutorial filters "plants and animals, categorized by their taxonomic rank", which is a similar case to filtering chemical compounds. The 4th is one of my previous answers to a similar question.
