4

I have 2 subforms on the main form. Sub_2 displays the details for the item chosen in sub_1, in a list. For both subform, I use the continue form, and they are bounded to 2 querys, with one field in common, say 'id'.

What I have done is adding code in the DClick event on sub_1 to set the sub_2's filter like

Me.Parent.sub_2.Form.Filter = "id=" & "'" & Me.Recordset!id & "'"

Then a requery

Me.Parent.sub_2.Form.Requery

But no luck, when I double click the record in sub_1, no change in sub_2. I have had a look on relevant questions, I think this should work...

Any suggestions? Maybe I should use other approach to do this?

PS: I'm sure Me.Parent.sub_2.Filter is changed, but requery doesn't work.

EDIT:

Thanks for answers. I find the problem, that is, the 'id' field is in the query, but it's not displayed on the subform, so maybe Access cannot use it.

And when the filter is changed, Access does a requery automatically.

darkjh
  • 2,821
  • 7
  • 35
  • 43

3 Answers3

6

You should not need to do anything with the filter property.

On the first subform, set the Link Master Fields and Link Child Fields properties as you would for a regular, single subform control on a form.

For the second subform, also set the Link Child Fields property as you would for a regular, single subform control. The Link Master Fields property will reference the control name of the first subform: [Subform 1 Control Name]!FieldName.

In the OnCurrent event of the first subform control, add the following line of VBA code:

Me.Parent.Controls![Subform 2 Control Name].Requery

Run the form. When you click a row in the first subform, the second subform will requery to display the child rows for the row selected in the first subform.

John Mo
  • 1,326
  • 10
  • 14
  • I found the above solution wouldn't work without some modification -- it needs to be `Me.Parent.Controls!SubformName.Form.Requery` (ie, insert a `Form.`) – Martin F Dec 11 '15 at 22:51
2

Set the FilterOn property to True after you assign the Filter expression.

Me.Parent.sub_2.Form.Filter = "id=" & "'" & Me.Recordset!id & "'"
Me.Parent.sub_2.Form.FilterOn = True
HansUp
  • 95,961
  • 11
  • 77
  • 135
0

You're going to need something like the following: 

Me!Subform2.Form.Requery  
'or  
Forms!Mainform!Subform1.Form!Subform2.Form.Requery

I didn't have time to test this so I can't tell you for sure if Subform1 and Subform2 should be the name of the actual subform or merely the Subform control/container. I think it's the latter.

Here's an excellent source for these kinds of questions:
http://access.mvps.org/access/forms/frm0031.htm

My only complaint with that page is that they do not cover calling methods, subs/functions, or referring to public variables. All of these are (or can be) slightly different than the scenarios listed there.

HK1
  • 11,941
  • 14
  • 64
  • 99