Subform Filter by VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,
I am trying to change the filter on a subform via code at the click of a
button. The subform will always have [Project Manager] as the master and
child fields, but I want to toggle b/w the following WHERE conditions at the
click of a button.

[Current Status]= '12. Project Complete'
[Current Status]<>'12. Project Complete'

These will change the data in the sub b/w current and completed projects.

I am sure I can figure out the IF function just fine, but how do I set the
filter of a subform when clicking a button on the parent?

Me.Subform.Filter seems like it should work but doesn't. Am I missing
something?

Thanks for all the help in advance.

Adam
 
You need two lines, try this:

Me.Subform.Form.Filter = "[Current Status]= '12. Project Complete'"
Me.Subfrom.Form.FilterOn = True 'applies the filter

Remember, when you use Me.Subform you are actually referencing a Subform
Control (a container for your subform) not the actual subform. So you need to
use .Form to reference the current form in that control. A Subform Control
does not have a filter property but the form that it contains does...see what
I mean?

You can have one Subform Control on a form and change its Source Object and
Master/Child Link properties to show different subforms within it. This is a
better way of handling multiple subforms that you want to appear in the same
place as overlapping controls can give you problems in your forms.

Steve
 
Steve has given you a good answer, but if you filter the main form as well
as the subfom, you will run into problems.

Details:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SteveM said:
You need two lines, try this:

Me.Subform.Form.Filter = "[Current Status]= '12. Project Complete'"
Me.Subfrom.Form.FilterOn = True 'applies the filter

Remember, when you use Me.Subform you are actually referencing a Subform
Control (a container for your subform) not the actual subform. So you need
to
use .Form to reference the current form in that control. A Subform Control
does not have a filter property but the form that it contains does...see
what
I mean?

You can have one Subform Control on a form and change its Source Object
and
Master/Child Link properties to show different subforms within it. This is
a
better way of handling multiple subforms that you want to appear in the
same
place as overlapping controls can give you problems in your forms.

Steve

F1stman said:
Hello all,
I am trying to change the filter on a subform via code at the click of a
button. The subform will always have [Project Manager] as the master and
child fields, but I want to toggle b/w the following WHERE conditions at
the
click of a button.

[Current Status]= '12. Project Complete'
[Current Status]<>'12. Project Complete'

These will change the data in the sub b/w current and completed projects.

I am sure I can figure out the IF function just fine, but how do I set
the
filter of a subform when clicking a button on the parent?

Me.Subform.Filter seems like it should work but doesn't. Am I missing
something?

Thanks for all the help in advance.

Adam
Click to expand...
Click to expand...
 
That worked like a charm. Thank you for the very useful information.

Adam

SteveM said:
You need two lines, try this:

Me.Subform.Form.Filter = "[Current Status]= '12. Project Complete'"
Me.Subfrom.Form.FilterOn = True 'applies the filter

Remember, when you use Me.Subform you are actually referencing a Subform
Control (a container for your subform) not the actual subform. So you need to
use .Form to reference the current form in that control. A Subform Control
does not have a filter property but the form that it contains does...see what
I mean?

You can have one Subform Control on a form and change its Source Object and
Master/Child Link properties to show different subforms within it. This is a
better way of handling multiple subforms that you want to appear in the same
place as overlapping controls can give you problems in your forms.

Steve

F1stman said:
Hello all,
I am trying to change the filter on a subform via code at the click of a
button. The subform will always have [Project Manager] as the master and
child fields, but I want to toggle b/w the following WHERE conditions at the
click of a button.

[Current Status]= '12. Project Complete'
[Current Status]<>'12. Project Complete'

These will change the data in the sub b/w current and completed projects.

I am sure I can figure out the IF function just fine, but how do I set the
filter of a subform when clicking a button on the parent?

Me.Subform.Filter seems like it should work but doesn't. Am I missing
something?

Thanks for all the help in advance.

Adam
Click to expand...
Click to expand...
 
Back
Top