Filter subform date from form On button Click

[user9]

This should be straightforward, I'm just struggling with syntax. I very simply want to click a button on a main form to view only record in the subform where the [Date_field] is prior to today.

Several posts show conflicting syntax for this. Here is what I have:
Dim strWhere As String
strWhere = "[Date_field] < #" & Date & "#"


Form_frmRecords.Filter = strWhere
Form_frmRecords.FilterOn = TrueThe recordsource for the subform is a query, I'm wondering if that is hosing it up.

[NTC]

the syntax looks ok. you shouldn't be using 'date' as a field name as that is a reserved word.... and the fact that the record source is a query isn't an issue

add a requery statement at the bottom of your code stack to force & conclude the subform to requery and see if that works....

[June7]

The code is behind main form? Would be easier to have the button on the subform.

Bing: access vba filter subform http://www.access-programmers.co.uk/...d.php?t=211464

As the example shows, need to reference the subform container control. Best is to name the container control different from the source object it holds.

It is possible that won't work with a table or query source object and will need a form as source object.

[user9]

The code is behind main form? Would be easier to have the button on the subform.

Bing: access vba filter subform http://www.access-programmers.co.uk/...d.php?t=211464

As the example shows, need to reference the subform container control. Best is to name the container control different from the source object it holds.

It is possible that won't work with a table or query source object and will need a form as source object.

Thank you both for the responses. I tested out a button on the subform as well, but it didn't return any results other than the PK value on the first record in the table.

I did get the following VBA to work from the button on the Main form (to show records in the subform).
Form_frmRecords.RecordSource = "qryRespondBy"
strWhere = "[Respond_by_Date] < #" & Date & "#"
Me.Filter = strWhere
Me.FilterOn = True
Me.[frmRecords].Form.Requery

However, the Record filter on the returned values is on the Main form (whereas another search I have has the Records filtered through the Subform). Not a huge deal, I'd just prefer consistency.

[June7]

I am confused. You say you want to apply filter to subform yet your code references the main form Filter property.

The mainform and subform each joined to different tables related by PK/FK fields? If the subform is synchronized to the main form with the Master/Child Links properties, only records synchronized by the PK/FK fields will display.

[user9]

I am confused. You say you want to apply filter to subform yet your code references the main form Filter property.

The mainform and subform each joined to different tables related by PK/FK fields? If the subform is synchronized to the main form with the Master/Child Links properties, only records synchronized by the PK/FK fields will display.

The code does reference the subform (the name of the main form is "frmMain" and the subform is "frmRecords"). The subform record source is a different query however, as I have two sets of search fields on the main form - one being to search for values in Main form unbound fields and the other being this filter.

[June7]

Still confused. The code builds a filter string but uses it with the main form Filter and FilterOn properties. Why would you requery the subform? The Master/Child Links properties of subform container control will automatically synchronize records.