473,561 Members | 3,139 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filtering a subform based on interactive dropdowns

8 New Member
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!

Attached Images
File Type: jpg Access_SubformFilteringIssue.jpg (114.5 KB, 569 views)
Jan 10 '21 #1
16 3537
32,564 Recognized Expert Moderator MVP
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.
Jan 10 '21 #2
8 New Member
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!
Jan 10 '21 #3
457 Recognized Expert Moderator Contributor
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'
Jan 10 '21 #4
8 New Member
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!
Jan 10 '21 #5
457 Recognized Expert Moderator Contributor
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'
Jan 10 '21 #6
8 New Member
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!
Jan 10 '21 #7
32,564 Recognized Expert Moderator MVP
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub DoFilter(frmVar As Form)
  2.     Dim strWhere As String
  4.     With Me
  5.         If Not IsNull(.cboNum) Then
  6.             strWhere = strWhere & " AND ([NumField]=%N)"
  7.             strWhere = Replace(strWhere, "%N", .cboNum)
  8.         End If
  9.         If Not IsNull(.cboDate) Then
  10.             strWhere = strWhere & " AND ([DateField]=#%D#)"
  11.             strWhere = Replace(strWhere, "%D", Format(.cboDate, "yyyy\-m\-d"))
  12.         End If
  13.         If Not IsNull(.cboString) Then
  14.             strWhere = strWhere & " AND ([StringField]='%S')"
  15.             strWhere = Replace(strWhere, "%S", .cboString)
  16.         End If
  17.         If strWhere > "" Then strWhere = Mid(strWhere, 6)
  18.     End With
  19.     With frmVar
  20.         If .Filter <> strWhere Then
  21.             .Filter = strWhere
  22.             .FilterOn = (strWhere > "")
  23.         End If
  24.     End With
  25. End Sub
Jan 11 '21 #8
8 New Member
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!

Jan 11 '21 #9
32,564 Recognized Expert Moderator MVP
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.
Jan 11 '21 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the records the subform's table has. I cannot put the subform as Data Entry because I cannot print the main form & subform together if the subform is...
by: mnms | last post by:
Hi, I'm trying to create an "overview" form. And I haven't been able to figure out how to get Access to do what I want yet. Basicly what I want, is to populate a subform based on a field selection. In other words, I have several fields like colour, shape, location etc. on my Main form. Now I want to add a subform to my unbound mainform,...
Scott Price
by: Scott Price | last post by:
Hello again! Running MS Access 2003 on WinXPproSP2. Now I'm trying to re-filter my subform based on the currently shown Year field (the filter applied on open restricts to 15 records relating to the year 1990). I've added a command button to call the code designed to re-filter to the 13 or so records according to the next year (1991 in this...
by: NJonge01 | last post by:
Great thanks to all the helpful responses I've read! Recently using MS Access after a lengthy (7-10 years) away from the tool. I apologize for posting a question that for all intents & purposes appears to have been answered several times. Unfortunately, I've tried nearly a dozen approaches that seem to work for others on this board with similar...
by: shreyansghia | last post by:
Hello , I am using MS Access 2007. Now if i ve a subform based on a table, filtering the subform is extremely easy . I only ve to click the relevant column head on the subform and check/uncheck the required records (clicking on the column head would yeild a drop down list). However , since my subform is based on a query , i cannot avail...
by: daleshei | last post by:
I have a combo box with list all the queries I have on my database: Form is called: frm_qry_slct Combo box (Unbound): Combo23 RowSourceType: Table/Query RowSoource:SELECT FROM MSysObjects WHERE =5 And Like "qry_#*" ORDER BY ; Then my SubForm
by: csolomon | last post by:
Happy Hump Day! I was wondering if it would be possible to some how resize my subform based on the number of records it holds. I have a subform that I populate via a non-updatable query. It just shows the user what he/she has ordered. Based on this information, I would like for the user to just be able to view the information with out...
by: kashif73 | last post by:
Hi, I have a subform (datasheet view) on my Mainform, where a user can enter information. I need help on how to display the required number of rows in my subform based on the number selected in the dropdown box on my mainform. Please see the attached GIF. for example if a user selects 5 from the dropdown then automatcially the subform below...
by: hbaf208 | last post by:
I have a combobox on a subform that is based on an SQL that uses a listbox on the unbound parent form as the criteria. When the form is first loaded, it works perfectly, limiting the dropdowns to only those associated with the listbox. However, when I update the listbox, I cannot get the combobox to requery. I've tried requery AfterUpdate on...
Jerry Maiapu
by: Jerry Maiapu | last post by:
I have a many to many relationship implemented into a db with 2 parent forms with their respective child forms. ( 3 tables, 1 link table) for each subform I have a lookup combo on FK Field but combo datasource is based on original PK from table data. That means data cannot be entered on the subform but is collected from the combo. ...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.