how to refer to a subform when setting its filter property

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'm trying to set the filter property of a subform from a command button
click event in the main form, but I'm having trouble specifying that I want
the filter to be applied to the subform.

Let's say my form is named "frmMain," my subform is named "frmMainSub" and
my subform control is named "ctlMainSub." I've tried different combinations
like

Forms!frmMain!frmMainSub
Forms!frmMain!ctlMainSub!frmMainSub
Forms!frmMain!ctlMainSub.forms!frmMainSub and
Forms!frmMain.forms!frmMainSub,

but nothing has worked so far. Can anyone tell me how to refer to the
subform in the click event of a command button in the main form?

Thanks in advance.

Paul
 
Paul,

Your frmMainSub may not be only a subform but also
a "child" to the frmMainSub.
Try this:
Open the frmMainSub form on design view;
activate the properties box;
click the very edge of the frmMainSub until the heading of
the properties is like "Subform/ Subreport: child14";
Then you can use the following as a reference:
[Forms]![frmMain]![Child14].Form!ctrlMainSub

Hope that helps,

Jacinto
 
Hi Paul

I had a similar problem, the format is:

frmMainSub.Form!ctlMainSub

HTH

Geraint
 
Jacinto is sending you on the right track - because it doesn't matter what
the name of the form is in one sense because it is a control on the main
form. So you need to see what the control name is - that's why I always call
them the same name as the actual form name.
But it sounded like you wanted to filter the recordset behind the subForm
didn't you?
You could set it up to toggle the view if you wanted:
In the OnClick event you just need something similar to this:
Me.[subFormName].Filter = Whatever filter you are creating
If Me.[subFormName]FilterOn = True then
Me.[subFormName]FilterOn = False
Else
Me.[subFormName].FilterOn = True
End If
Of course if you only want the filter on and never off - why not just set
the filter in the subform and turn it on permanently there?
Anyway - HTH rather than confuses
Tom
 
You need to use the subform controls 'form' property, like
this:-

Forms!frmMainfrm!ctlMainSub.Form

hth

chris
 
Thanks to Jacinto, Geraint, Tom and Chris. I tried all of the suggestions
for referring to a subform from a control on the main form, and none of the
suggestions worked. I've copied the suggested lines of code below, and
described the error messages I get when I'm trying to run the command:

first I'll reproduce the object definitions:

main form: frmMain
subform: frmMainSub
subform control: ctlMainSub

My objective is to filter the recordset in the subform based on certain
criteria set in a text box control on the main form, where the filter
property of that subform recordset is set by VBA code run by a command
button on the main form. My problem has been in properly referring to the
subform. I should mention that the main form has no record source, so the
recordset of the subform isn't linked to one in the main form with linked
Master and Child fields.

So again, here are the different formulations of the subform I've tried,
along with the error messages each one produced:

[Forms]![frmMain]![ctlMainSub].Form!ctlMainSub

produces the error message that Access can't find the field
'ctlMainSub.'

frmMainSub.Form!ctlMainSub

produces a Compile Error - variable not defined

so I modified it to

forms!frmMainSub.Form!ctlMainSub

and got the error message

"Object doesn't support this property or method."

Forms!frmMainfrm!ctlMainSub.Form

also produces the error message: "Object doesn't support this property
or method."


Thanks to everyone for taking a look at this. I'm hoping these messages
might give someone an idea of what I can do to get this code to work.

Paul
 
OK - I'm assuming I am reading this right and that the ctlMainSub is
actually the sub form in the main form.
With that in mind then, in the OnClick event of the button on the main form
put:
Me.ctlMainSub.Form.Filter = "ControlOnSubFormToMatchCriteria =
[txtBoxWithFilterParameters]"
Me.ctlMainSub.Form.FilterOn = True
Again if you want to toggle the filter on and off simply use a conditional
statement to check its current status when the button is pushed.
HTH
Tom
 
It works!!!

Thanks so much, Tom, and thanks for taking the time to build the example.
 
Back
Top