By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

passing filter to a sub form

P: n/a
Hi everyone,

I have a sub form that references a query to get the results. However,
what I want to do is filter the results further based on a certain
criteria. How can I tell the sub form to filter results based on a
certain criteria.

I tried using the Filter and OrderBy fields in the designer but they
seem to have no affect!

Thanks,
Anja

Oct 15 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi everyone,

I have a sub form that references a query to get the results. However,
what I want to do is filter the results further based on a certain
criteria. How can I tell the sub form to filter results based on a
certain criteria.

I tried using the Filter and OrderBy fields in the designer but they
seem to have no affect!
You also need to the FilterOn property to True. You need to do that in code
since that property is not exposed in the property sheet.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 15 '06 #2

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi everyone,

I have a sub form that references a query to get the results. However,
what I want to do is filter the results further based on a certain
criteria. How can I tell the sub form to filter results based on a
certain criteria.

I tried using the Filter and OrderBy fields in the designer but they
seem to have no affect!

You also need to the FilterOn property to True. You need to do that in code
since that property is not exposed in the property sheet.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Hi,

Thanks for replying. How can I access this subform from VBA through
code.

So, in my forms window, I have 2 forms.

"Transactions by category" and
"TransactionsByCategory_V" subform. The category form refers to the
category_V subform.

How do I refer to the subform from code? I tried doing
[Forms]!["TransactionsByCategory_V"].FilterOn= true but that does not
seem to work. IT says it cannot find this form...

Cheers,
Anja

Oct 15 '06 #3

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Hi everyone,

I have a sub form that references a query to get the results. However,
what I want to do is filter the results further based on a certain
criteria. How can I tell the sub form to filter results based on a
certain criteria.

I tried using the Filter and OrderBy fields in the designer but they
seem to have no affect!

You also need to the FilterOn property to True. You need to do that in code
since that property is not exposed in the property sheet.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Hi,

Thanks for replying. How can I access this subform from VBA through
code.

So, in my forms window, I have 2 forms.

"Transactions by category" and
"TransactionsByCategory_V" subform. The category form refers to the
category_V subform.

How do I refer to the subform from code? I tried doing
[Forms]!["TransactionsByCategory_V"].FilterOn= true but that does not
seem to work. IT says it cannot find this form...

Cheers,
Anja

Oct 15 '06 #4

P: n/a
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Thanks for replying. How can I access this subform from VBA through
code.

So, in my forms window, I have 2 forms.

"Transactions by category" and
"TransactionsByCategory_V" subform. The category form refers to the
category_V subform.

How do I refer to the subform from code? I tried doing
[Forms]!["TransactionsByCategory_V"].FilterOn= true but that does not
seem to work. IT says it cannot find this form...
A from displayed in a subform is not considered "open" by Access and will not be
found in the forms collection. You have to refer to it via the subform control
on the parent form...

[Forms]!SubformControlName.Form.FilterOn= True

Note that if the subform was created with the form wizard or by dragging a form
onto the parent form, the subform control will *usually* have the same name as
the form contained within, but that is not guaranteed. You need the name of the
control in that statement.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 15 '06 #5

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Thanks for replying. How can I access this subform from VBA through
code.

So, in my forms window, I have 2 forms.

"Transactions by category" and
"TransactionsByCategory_V" subform. The category form refers to the
category_V subform.

How do I refer to the subform from code? I tried doing
[Forms]!["TransactionsByCategory_V"].FilterOn= true but that does not
seem to work. IT says it cannot find this form...

A from displayed in a subform is not considered "open" by Access and will not be
found in the forms collection. You have to refer to it via the subform control
on the parent form...

[Forms]!SubformControlName.Form.FilterOn= True

Note that if the subform was created with the form wizard or by dragging a form
onto the parent form, the subform control will *usually* have the same name as
the form contained within, but that is not guaranteed. You need the name of the
control in that statement.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
This is really confusing!! I feel stupid...

So, the name of the subform control is given as
"TransactionsByCategory_V subform" and the name of the parent form is
"Transctions by category".

So, I am trying now:

[Forms]!["Transactions by category"]!["TransactionsByCategory_V
subform"].Form.FilterOn = True

However, it simply fails at finding even ""Transactions by category"

Thanks,

Anja

Oct 15 '06 #6

P: n/a

"Anja" <an*******@googlemail.comwrote in message
news:11********************@f16g2000cwb.googlegrou ps.com...
>
This is really confusing!! I feel stupid...

So, the name of the subform control is given as
"TransactionsByCategory_V subform" and the name of the parent form is
"Transctions by category".

So, I am trying now:

[Forms]!["Transactions by category"]!["TransactionsByCategory_V
subform"].Form.FilterOn = True

However, it simply fails at finding even ""Transactions by category"
There should not be quotes around the names.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 15 '06 #7

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11********************@f16g2000cwb.googlegrou ps.com...

This is really confusing!! I feel stupid...

So, the name of the subform control is given as
"TransactionsByCategory_V subform" and the name of the parent form is
"Transctions by category".

So, I am trying now:

[Forms]!["Transactions by category"]!["TransactionsByCategory_V
subform"].Form.FilterOn = True

However, it simply fails at finding even ""Transactions by category"

There should not be quotes around the names.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
No, still no luck. I do not think I am smart enough to use this thing.
verge of giving up here..

However, I fail to see the logic behind this thing... t is designed in
a pretty insane way! Sorry for venting but my whole Sunday has been
spent doing this.

So, my statement looks like:
[Forms]![Transactions by category]![TransactionsByCategory_V
subform].TransactionDate.Form.FilterOn = True

Transactions by category is one of the parent forms
TransactionsByCategory_V subform is a sub form

TransactionDate is a control on the subform that is tied to a database
field...

In any case, the whole thing cannot even find the parent form
Transactions by category..

Cheers,
Anja

Oct 15 '06 #8

P: n/a
"Anja" <an*******@googlemail.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...
>
No, still no luck. I do not think I am smart enough to use this thing.
verge of giving up here..

However, I fail to see the logic behind this thing... t is designed in
a pretty insane way! Sorry for venting but my whole Sunday has been
spent doing this.

So, my statement looks like:
[Forms]![Transactions by category]![TransactionsByCategory_V
subform].TransactionDate.Form.FilterOn = True

Transactions by category is one of the parent forms
TransactionsByCategory_V subform is a sub form

TransactionDate is a control on the subform that is tied to a database
field...

In any case, the whole thing cannot even find the parent form
Transactions by category..
Divide and conquer. If you have a form named "Transactions by category" and
that form is currently opened then this statement will definitely work to make a
reference to that form...

Forms![Transactions by category]

If that form has on it a subform control named "TransactionsByCategory_V
subform" then this statement should correctly reference it...

Forms![Transactions by category]![TransactionsByCategory_V subform]

The syntax above will refer to the subform control in exactly the same manner
that you could refer to any other control like a TextBox or ComboBox. Now to
reference the form being displayed within that subform control you need to add
the Form property...

Forms![Transactions by category]![TransactionsByCategory_V subform].Form

In your posted statement you had the ".Form" in the wrong location. It needs to
be immediately after then name of the subform control. The name of the actual
form inside the subform control is not used at all.

Okay... now that I have a statement that is referring to the form inside the
subform control referring to properties of that form is now accomplished by
adding to the end of that statement...

Forms![Transactions by category]![TransactionsByCategory_V
subform].Form.FilterOn

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Oct 15 '06 #9

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...

No, still no luck. I do not think I am smart enough to use this thing.
verge of giving up here..

However, I fail to see the logic behind this thing... t is designed in
a pretty insane way! Sorry for venting but my whole Sunday has been
spent doing this.

So, my statement looks like:
[Forms]![Transactions by category]![TransactionsByCategory_V
subform].TransactionDate.Form.FilterOn = True

Transactions by category is one of the parent forms
TransactionsByCategory_V subform is a sub form

TransactionDate is a control on the subform that is tied to a database
field...

In any case, the whole thing cannot even find the parent form
Transactions by category..

Divide and conquer. If you have a form named "Transactions by category" and
that form is currently opened then this statement will definitely work to make a
reference to that form...

Forms![Transactions by category]

If that form has on it a subform control named "TransactionsByCategory_V
subform" then this statement should correctly reference it...

Forms![Transactions by category]![TransactionsByCategory_V subform]

The syntax above will refer to the subform control in exactly the same manner
that you could refer to any other control like a TextBox or ComboBox. Now to
reference the form being displayed within that subform control you need to add
the Form property...

Forms![Transactions by category]![TransactionsByCategory_V subform].Form

In your posted statement you had the ".Form" in the wrong location. It needs to
be immediately after then name of the subform control. The name of the actual
form inside the subform control is not used at all.

Okay... now that I have a statement that is referring to the form inside the
subform control referring to properties of that form is now accomplished by
adding to the end of that statement...

Forms![Transactions by category]![TransactionsByCategory_V
subform].Form.FilterOn

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
I see what is going on! The form has to be open for all this to work.
But I want to open the form after the filter has been applied to it!

Oct 15 '06 #10

P: n/a
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
I see what is going on! The form has to be open for all this to work.
But I want to open the form after the filter has been applied to it!
Can't do it. Putting the code in the Open or Load event of the parent form
might work for you though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 15 '06 #11

P: n/a

Rick Brandt wrote:
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

I see what is going on! The form has to be open for all this to work.
But I want to open the form after the filter has been applied to it!

Can't do it. Putting the code in the Open or Load event of the parent form
might work for you though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Hi,

Thanks. I guess I will design it in a different way.

Cheers,
Anja

Oct 15 '06 #12

P: n/a
"Anja" <an*******@googlemail.comwrote in
news:11*********************@m7g2000cwm.googlegrou ps.com:
However, I fail to see the logic behind this thing... t is
designed in a pretty insane way!
It's an example of object orientation and instantiation of an
instance of a class.

A form is a wrapper around a class module.

Class modules can have instantiations that are independent of the
class definition.

For instance, you could have:

Dim cls1 As New clMyClass
Dim cls2 As New clMyClass

In that case you have two instances of the same class. Each instnace
will have independent properties, even though it is derived from the
same class definition.

In the case of a subform, you have an instantiation of the subform
class as a child object of the main form, and it only exists as a
child object of the main parent form.

Technically, it's actually a child object of a subform control on
the main form.

Thus, the instance of the form that you are working with is:

Form!ParentForm!SubformControl.Form

When you're working in the class module of the parent form, you can
refer to it thus:

Me!SubformControl.Form

One thing that confuses many people is that when you drop a subform
on a main form, the name of the subform control is identical to the
name of the subform itself. While this can be helpful, it is also
confusing, because you've got two completely different things using
exactly the same name, which tends to make it confusing to
distinguish the two.

I never let my subform controls have the same name as the subform
embedded in them. If the subform is called subMySubForm, I will name
the subform control MySubForm.

There is nothing inconsistent about the naming conventions here or
about the way you refer to data and properties of a subform. It is
completely consistent with the whole structure of Access, which is
based on subclassing and instantiation of various objects.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 15 '06 #13

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:SA******************@newssvr13.news.prodigy.c om:
Okay... now that I have a statement that is referring to the form
inside the subform control referring to properties of that form is
now accomplished by adding to the end of that statement...

Forms![Transactions by category]![TransactionsByCategory_V
subform].Form.FilterOn
And if you're setting multiple properties, it's easiest to use a
With block:

With Me!SubformControl.Form
.Filter = ...
.FilterOn = True
End With

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 15 '06 #14

P: n/a
"Anja" <an*******@googlemail.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
The form has to be open for all this to work.
But I want to open the form after the filter has been applied to
it!
It's not clear why you want it this way.

If you want to display a large result set and then filter it based
on choices made by the user of the parent form, then there's no
issue.

If, on the other hand, you want to have the subform be empty until
various filter properties have been set, you'd be better off just
setting the Recordsource of the subform, with an appropriate WHERE
clause (i.e., the same as the filter would have been).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 15 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.