473,382 Members | 1,387 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

passing filter to a sub form

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
14 4066
"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

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

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
"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

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

"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

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
"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

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
"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

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
"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
"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Thomas Philips | last post by:
To experiment with filtering, I define a function f(x,k) as follows >>> def f(x,k=2): return x%k==0 I can check that it works by typing >>> f(10,3) False Now, I try to filter a range using...
11
by: Dthmtlgod | last post by:
I am having a little difficulty in passing a value from a page to another page. I am going to provide excerpts from the code. I think I am close. This is from Page1.ASP <% Set Conn =...
1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
4
by: entitledX | last post by:
Hi, I'm trying to use the HDF library to read a few HDF files that I need to process. The data in each file varies in rows, but the columns remain constant. Because of that, I had dynamically...
28
by: Skeets | last post by:
i'm passing session and hidden variables between pages. not to mention post values. i'm a little concerned that someone with sufficient knowledge could spoof these vlaues and manipulate the...
2
by: Mark Drummond | last post by:
Hi all. I've been using Perl for many years now, but I am a "use it and learn it as you need it" type. I having some trouble passing a list to the "search" subroutine from Net::LDAP. I am trying...
4
by: tbayse | last post by:
Hello, I have a question about making multiple selection queries in Access. I am running windows XP and Access 2003. Up until this point I had a form where a user would make single selections from...
2
by: Shashank | last post by:
Hi, I am passing a tcpdump filter to a function which compiles the filter using pcap_compile and then sets it. Here is the filter, ip>=0x0000 and ip <=0xd9295a3 and ip >=0x0000 and ip...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.