set filteron in subform (1 Viewer)

[DataMiner]

Hi, I have a checkbox on parent form ProductInfoMaster, and would like its after-update event to change the filteron property on subform ProductInfoSub. here's my code:

Private Sub includeobso_AfterUpdate()
If Me.includeobso = True Then
Me.Noun.RowSource = "SELECT ProductInfoMaster.Noun FROM ProductInfoMaster GROUP BY ProductInfoMaster.Noun;"
Forms!productinfomaster!productinfosub.FilterOn = False
Else
Me.Noun.RowSource = "SELECT ProductInfoMaster.Noun FROM ProductInfoMaster where status<>'obsolete' GROUP BY ProductInfoMaster.Noun;"
Forms!productinfomaster!productinfosub.FilterOn = True
End If
Me.productinfosub.Requery
End Sub

but at the filteron step, i get error "object doesn't support this property or method".

What am i doing wrong?

I also tried resetting the subforms recordsource instead, and i get the same error.

using access 2007.


thanks for any help.

 

[boblarson]

When using vba to refer to a subform you need to use all of the parts:

Me.YourSubformControlName.Form.Filter

So YourSubformControlName is the name of the control on the main form which houses the subform, it isn't the subform name (unless the control and the subform control share the same exact name).

And the .Form. part is required as shown so Access knows you want a property or method of the subform itself and not of the subform control.

 

[DataMiner]

thanks, i've changed my code to:

Private Sub includeobso_AfterUpdate()
If Me.includeobso = True Then
Me.Noun.RowSource = "SELECT ProductInfoMaster.Noun FROM ProductInfoMaster GROUP BY ProductInfoMaster.Noun;"
Me.productinfosub.Form.FilterOn = False
Else
Me.Noun.RowSource = "SELECT ProductInfoMaster.Noun FROM ProductInfoMaster where status<>'obsolete' GROUP BY ProductInfoMaster.Noun;"
Me.productinfosub.Form.FilterOn = True
End If


Me.productinfosub.Form.Requery
MsgBox Me.productinfosub.Form.FilterOn

End Sub

the msgbox indicates that the filter has been applied, but the data doesn't. the filter looks like this:

"status<>'obsolete'"

but here's a sample of the output:

Noun Status Rev ProcessFlow
658-4003 obsolete a e
658-4003 obsolete T E
658-4003 obsolete V E
658-4003 obsolete 001 E
658-4003 obsolete 002 E
658-4003-01 obsolete n E
658-4003-01 obsolete p E

 

[DataMiner]

ok, i figured it out. contrary to what on-line help seems to say, the filter should be
status<>'obsolete'

NOT
"status<>'obsolete'"