By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Cascaded Form Filtering with Subforms

P: 5
I need to use unbound controls in a form header to filter a form. There is a very well written insight that describes filtering forms with unbound controls in the form header. (http://bytes.com/topic/access/insigh...form-filtering) However, it clearly states that the example is simple and does not include subforms. My form has two subforms each with a single combo box control. I need to filter records in my form based on values in my subforms and I need them to cascade. Help?
Dec 8 '11 #1

✓ answered by NeoPa

So, each project can be related to multiple rivers, as well as multiple communities, and also multiple projects can be related to any of these same rivers or communities. Nice. In that case the filter string for the main form would be in the format :

Expand|Select|Wrap|Line Numbers
  1. ([Project_ID] In(SELECT [Project_ID]
  2.                  FROM   [Project_River]
  3.                  WHERE  ([River_ID] = XXX)))
Where XXX is the value returned from your River ComboBox filter. Does that make it clearer? I see nothing here that relates to a cascading feature, but that may be because you simplified things. Also, this code assumes all your ID fields are numeric.

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
That sounds interesting, if somewhat more complicated than the details provided can support.

Can you give more details of exactly what connects to what and how. What depends on what. What triggers what. Such an understanding is critical to know how this should best be handled. Object names (Form names; Control names; Table names and Field names) also make it much easier to work within an explanation of your situation.

If you prefer, you can attach a copy of the database you're working on with your setup so far (See Attach Database (or other work) for details on how to do that).
Dec 9 '11 #2

100+
P: 759
@Jewel8368
"I need to filter records in my form based on values in my subforms and I need them to cascade"

For me sound as a cycle.
Maybe I misunderstand. If not, that task can't be accomplished.
Dec 9 '11 #3

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
For me sound as a cycle.
Indeed. A big reason why more details are needed. Maybe it's just a problem mis-expressed. It's not always easy to explain things clearly, even in one's own mother tongue.
Dec 9 '11 #4

P: 5
Ok, my first priority is to filter my form based on combo box values. I can look at the cascading part after.

I have a table called projects and it has fields 'project_id', 'effective_date', 'issued_date', 'name', and 'type'. Each project could be related to multiple rivers and communities. I have a table for each: a river table that has fields 'river_id' and 'river' and a community table that has fields 'community_id' and 'community'. Then, to accommodate the two many to many relationships I have two more tables: a project_community table with fields 'project_id' and 'community_id' and a project_river table with 'project_id' and 'river_id'.

I have a form for adding projects and on this form I have 2 subforms. One that has a combo box for adding related rivers and another also with a combo box for adding related communities.

I need to add an unbound combo box in my form header that contains community values. When a value is selected, my form will filter to show only projects that are related to the selected value through the project_community table.
Dec 13 '11 #5

NeoPa
Expert Mod 15k+
P: 31,186
So, each project can be related to multiple rivers, as well as multiple communities, and also multiple projects can be related to any of these same rivers or communities. Nice. In that case the filter string for the main form would be in the format :

Expand|Select|Wrap|Line Numbers
  1. ([Project_ID] In(SELECT [Project_ID]
  2.                  FROM   [Project_River]
  3.                  WHERE  ([River_ID] = XXX)))
Where XXX is the value returned from your River ComboBox filter. Does that make it clearer? I see nothing here that relates to a cascading feature, but that may be because you simplified things. Also, this code assumes all your ID fields are numeric.
Dec 13 '11 #6

P: 5
I know it's been a long time but I'm ready to get back to this issue. I have 3 controls in the header to filter my form (Project Name keyword, type, and year). I have a button with the on click event code shown below to filter the form. I need to add a combo box for community and a combo box for flooding source to filter by. These two relations are as discussed previously. There can be a project with many communities and flooding sources. These values are stored in 2 subforms. I'm stuck on them being subforms and the relationships being in different tables.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Apply_Filter_Click()
  2. Dim strFilter As String
  3. strFilter = ""
  4.  
  5. If Me.fProjectName.Value = "" Or IsNull(Me.fProjectName) Then
  6. Else
  7. strFilter = "projectname Like '*" & Me.fProjectName & "*'"
  8. End If
  9.  
  10. If Me.fType.Value = "" Or IsNull(Me.fType) Then
  11. ElseIf strFilter = "" Then
  12. strFilter = strFilter & "type = '" & Me.fType & "'"
  13. Else
  14. strFilter = strFilter & "And type = '" & Me.fType & "'"
  15. End If
  16.  
  17. If Me.fYear.Value = "" Or IsNull(Me.fYear) Then
  18. ElseIf strFilter = "" Then
  19. strFilter = strFilter & "effectiveyear =" & Me.fYear & " OR issuedyear =" & Me.fYear
  20. Else
  21. strFilter = strFilter & "And (effectiveyear =" & Me.fYear & " OR issuedyear =" & Me.fYear & ")"
  22. End If
  23.  
  24. Me.Filter = strFilter
  25. Me.FilterOn = True
  26.  
  27. End Sub
Feb 28 '12 #7

Post your reply

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