0

I've got an MS Access 2013 form containing a child form within a child form (a grandchild), both in datasheet view.

Now I've got some controls on the main form with which one should be able to filter the records in both the child and the grandchild form.

Filtering the child form works totally fine with

Dim strSQL As String
strSQL = ...
Me!child.Form.RecordSource = strSQL
Me!child.Requery

But if I want to set up filters for the grandchild with

Dim strSQL As String
strSQL = ...
Me!child.Form!grandchild.Form.RecordSource = strSQL
Me!child.Form!grandchild.Requery

I get error 2455. With this question I solved the error by expanding one or more of the subform's rows.

But I now want to set the RecordSource for every grandchild row, while my current code is only working for the very first row, independent of which row I'm expanding/collapsing.

How do I change the RecordSource for every subsubform record in my main form, at best without manually expanding any row before filtering?

EDIT: Gustav provided a no-code solution that helped me. Nonetheless I wonder how to generally change form properties of a grandchild from the master form for all instances.

Community
  • 1
  • 1
Nikno
  • 119
  • 1
  • 8

1 Answers1

1

You don't need to adjust the recordsource.

Adjust the Master/Child link fields of the grandchild subform control, and the filtering will be automatic.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • If I got you right, this connects the subform to a specific field/ID of the main form. I don't want that, I want to display records out of the subform that meet certain criteria, using the WHERE clause in the RecordSource. – Nikno Feb 13 '17 at 13:26
  • You can do it either way. However, using _Master/Child_ link fields is a zero code solution. – Gustav Feb 13 '17 at 14:00
  • Say I want to display all data where in the subsubform `field1=foo` and `field2=bar`, how do I do that with link fields? – Nikno Feb 13 '17 at 14:06
  • Set the Master fields to include the controls having "foo" and "bar". Set the child field to include the fields Field1,Field2. – Gustav Feb 13 '17 at 14:13
  • Oh well, I didn't know you could link multiple fields. Thank you very much! Although I still wonder how to do it with code. – Nikno Feb 13 '17 at 14:22
  • 1
    There is a little trick: If you filter on, say, three fields: `Field1,Field2,Field3` and then want to "select all" for Field3, you can't just exclude it. Instead adjust the Master/Link fields to filter on _two_ fields only: `Field1,Field2,Field2`. To filter on the foreign key only: `Field1,Field1,Field1`. – Gustav Feb 13 '17 at 14:56