Setting the filter value on a subform

[nathan2314]

Hi All, :hi:
I'm stuck spinning my wheels on trying to figure out how to access a subform filter property value from a totally different form. So on the form that I'm on I would click a button and when this button is clicked, one of the things I need to happen is that a subform on a different form is set to a certain value.

Appreciate any help!!

[Kafrin]

So you have two forms open at the same time and they each have a subform. You want to click a button on one subform and this will filter the subform on the other form. Is this correct?

Assuming this is correct, you need to put something like this in the OnClick event of your button...


With Forms!MainForm2!SubformControlName2.form
.Filter = "FieldName=" & Me!FieldToMatch
.FilterOn = True
End With


There's obviously a lot here that you need to personalise to your database.
~ MainForm2 is the form containing the subform that you want to filter.
~ SubformControlName2 is the name of the control that contins the subform you want to filter.
~ FieldName is the name of the field/control on the subform that you want to filter.
~ FieldToMatch is the name of the field/control on the subform that the button is on.

Note that the .Filter line will need to be adapted depending on the data type of the fields.
For a numeric field:
.Filter = "FieldName=" & Me!FieldToMatch
For a text field:
.Filter = "FieldName='" & Me!FieldToMatch & "'"
For a date field@
.Filter = "FieldName=#" & Format("m/d/yy", Me!FieldToMatch) & "#"

[CreganTur]

Kafrin's example is excellent, but there is another way to filter your subform.

If your subform relies on the value of an object on your main form, then you can use the following method:

Create a query (if you have not already done so) to act as the record source for your subform. In the correct criteria column that matches the criteria provided by your form's object, enter the following value:
[Forms]![FormName]![ObjectName]
Replace FormName with the form's actual name, and ObjectName with the object's name.

Then click on Queries-> Parameters. Enter the exact same value in the first column, then select the data type of the provided criteria in the second column.

Now, on your Form, make sure you have setup a routine to requery your subform's values whenever the object's value changes. Try either of these:
Me.Requery
-or-
Me.Subform.Requery
I can never remember which one works best.

HTH:thumb

[nathan2314]

Thanks!!
That helped me make it work! :thumb