Cascaded Form Filtering with Subforms

[Jewel8368]

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?

[NeoPa]

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).

[Mihail]

@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.

[NeoPa]

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.

[Jewel8368]

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.

[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 :

([Project_ID] In(SELECT [Project_ID]
                 FROM   [Project_River]
                 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.

[Jewel8368]

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.

Private Sub Apply_Filter_Click()
Dim strFilter As String
strFilter = ""
 
If Me.fProjectName.Value = "" Or IsNull(Me.fProjectName) Then
Else
strFilter = "projectname Like '*" & Me.fProjectName & "*'"
End If
 
If Me.fType.Value = "" Or IsNull(Me.fType) Then
ElseIf strFilter = "" Then
strFilter = strFilter & "type = '" & Me.fType & "'"
Else
strFilter = strFilter & "And type = '" & Me.fType & "'"
End If
 
If Me.fYear.Value = "" Or IsNull(Me.fYear) Then
ElseIf strFilter = "" Then
strFilter = strFilter & "effectiveyear =" & Me.fYear & " OR issuedyear =" & Me.fYear
Else
strFilter = strFilter & "And (effectiveyear =" & Me.fYear & " OR issuedyear =" & Me.fYear & ")"
End If
 
Me.Filter = strFilter
Me.FilterOn = True
 
End Sub