Filter 1st subform based on 2nd subform

[BHo15]

I have a parent form (just a shell) with a subform, that contains its own subform with related records. I would like to be able to search for records in the 2nd subform (through a recordset), and then when found, have both the 2nd subform, and the 1st subform filter.

I tried using Set RS = Me.1stSub.Form.2ndSub.Form.Recordsetclone, and I got error 2455 (invalid reference to the property Form/Report). I also tried Set RS = Me.1stSub.2ndSub.Form.Recordsetclone, and this did not work either (although not error 2455).

So...Question #1: How to I build a recordset to that 2nd subform? And Question #2: Any ideas how to make that 1st subform also filter once I have filtered the 1st subform?

[twinnyfo]

BHo15,

First, it's not "exactly" clear what you are trying to do. Let me try to summarize:

You have a Form. On this Form is a Subform. On that SubForm, you have another SubForm. I think I have that correct?

Here is my question: If you are searching records on that second SubForm, why do you need to filter it by the record you have found? Some further clarificaiton would be appreciated.

However, concerning your questions:

Question #1: How to I build a recordset to that 2nd subform?

If you are currently working on that second subform Me.RecordsetClone should work. If you are working on another Form, the best way is to use explicit references: Forms!MainForm.1stSub.Form.2ndSub.Form.Recordsetcl one. You can research details in this article on Referencing SubForms.

Question #2: Any ideas how to make that 1st subform also filter once I have filtered the 1st subform?

The answer to this is similar to the first. Once you determine your Filter Parameters, you can assign that filter by referencing that Form explicitly.

So, even though you have two questions, they are similar enough in nature that they canboth be answered on this same thread.

Hope this hepps!

[BHo15]

First of all, let me say that I am honored to have you answering my question twinnyfo. I was spending a lot of time on Bytes many years back, and always appreciated your work. And no...I'm not brown nosing. :)

Regarding your uncertainties... You have a Form. On this Form is a Subform. On that SubForm, you have another SubForm. I think I have that correct?

Here is my question: If you are searching records on that second SubForm, why do you need to filter it by the record you have found? Some further clarificaiton would be appreciated.

Yes on the first question. Parent form (just a shell with some buttons and combo boxes). Subform 1 contains data on tasks that are in progress. These tasks are called Actions. Subform 2 contains data on progress for those tasks. The progress notes are called Responses. So… Subform 1: Actions. Subform 2: Responses. This is a one to many relationship.

We have it working to search for a word in the Actions subform, and have the subform filter to only those actions that have that word in them. But the desire is to also look through the Responses subform. If that word is seen in any of the responses, then we would like the responses subform to filter, and then subsequently, the Actions subform would filter to only those actions that contain the filtered responses. Make sense?

I will try your set-up of the recordset next time I can get back to the database, and will let you know.

[twinnyfo]

Before I move too far ahead of myself, I want to clarify a few things. One has to do with structure, the other has to do with "possibilities."

Structure (Table/Field names are assumed)

tblActions
 ActionID  Action       Action Detail
1         Sweep Floor  Run the broom back and forth
2         Mop Floor    Run the mop back and forth
3         Kick Back    Do nothing
4         Go Fishing   Catch some bass

tblResponses
 ResponseID  ActionID  Response
1           1         Sweep faster
2           1         Get the corners
3           2         Great job
4           2         Don't slip!
5           3         Your moving too fast
6           3         Slow day on the job
7           4         Get a faster boat
8           4         Call it a day

In general, this is correct?

Then, on fsubActions there is a Text Box; someone enters a search item. The code searches through tblActions for this search word and filters only those records which contain that search word. If your subforms are built correctly, this "should" automatically filter fsubResponses properly.

This is all correct?

But the desire is to also look through the Responses subform. If that word is seen in any of the responses, then we would like the responses subform to filter, and then subsequently, the Actions subform would filter to only those actions that contain the filtered responses.

This is about possibilities.

A. When you enter the search word, do you want fsubActions to filter only those records with the search word, and then, based upon that filtered set of records, filter only those records in fsubResponses which also contain that search word?

OR

B. When you enter the search word, you want to filter only those records in fsubResponses which contain that search word and subsequently filter fsubActions based upon that set of records in fsubResponses?

Examples for A:
Search Word: "the"
fsubActions filters IDs 1 and 2; fsubResponses filters ID 2

Search Word: "do"
fsubActions filters ID 3; fsubResponses filters nothing

Search Word: "ing"
fsubActions filters ID 3; fsubResponses filters ID 5


Examples for B:
Search Word: "fast"
fsubResponses filters IDs 1, 5 and 7; fsubActions filters IDs 1, 3 and 4

Search Word: "job"
fsubResponses filters IDs 3 and 6; fsubActions filters IDs 2 and 3

Search Word: "day"
fsubResponses filters IDs 6 and 8; fsubActions filters IDs 3 and 4

Or, is there some other permutation that you are looking for?

I think this might be pretty straightforward, but I need to be sure of these things first.

[BHo15]

Good stuff twinnyfo. Here are my thoughts.

1) Your setup of structure was correct, with the exception of the search text box... This is on the parent form, not either of the subforms. And yes... When the Actions subform is filtered, the Responses subform is also filtered.
2) Your break down of Possibility A and B brought up some good thoughts. Initially I had in mind that Actions would be filtered first, and then it would look at Responses. This would be overkill, and likely counterproductive. Therefore, what I am thinking now, is there will be an option group beside the search box asking whether you wanted to search Actions or Responses.
3) So, since Actions is good, let's focus on searching Responses. So that would be B, and your description and examples were correct.

[twinnyfo]

Assuming an Option Group in which "1" searches the Responses:

Private Sub cmdSearch_Click()
On Error GoTo EH
    Dim strSQL As String
 
    If optSearch = 1 Then
        strFilter = "ActionID IN (" & _
            "SELECT ActionID " & _
            "FROM tblResponses " & _
            "WHERE Response Like '*" & Me.txtSearch & "*';)"
    Else
        ...
    End If
 
    With Forms!frmActionResponse.fsubActions.Form
        .Filter = strFilter
        .FilterOn = True
    End With
 
    Exit Sub
EH:
    MsgBox "There was an error searching for the text!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

First, I thinkif you filter by the Responses, you will get some whacked, and perhaps undesired results from the Actions subform. So, instead we filter the Actions subform and the Responses are automatically filtered because of the structural relationship.

If you look closely at the Filter String I've written we are filtering only the ActionIDs that are found in the Responses Table for which the responses contain the search item.

Choosing the other option, you can build a similar Filter String

I hope this hepps!

[BHo15]

EXCELLENT! I've never done a form filter with a SQL statement. But seeing that is possible, that opens a world of opportunities.

What you said about filtering the Action form instead of the Responses form makes all the sense. I agree... If the Action form is filtered, the Responses form would follow suit.

Thanks so much for the help!

[twinnyfo]

Glad I could be of service!

[Takes a bow]