Apply Filter to Subform

N

nybaseball22

Hello. I have been struggling with this project for over a week. I
think/hope I am almost done. I am using the below code on a main
form. I think everything is working properly except the apply
filter. I have one of 5 subforms open on a Form OnCurrent and "Type"
AfterUpdate. However, I want the apply filter to apply to what is in
the subform. I think it is applying to the Main form. I use the
subforms on several different forms, so I am trying to avoid having
the source for the subform be a filter based on the form that is open
because they all have different names (obviously).

Does anyone know how I can get the apply filter to work on the
subform?

Thank you very much.

If IsNull(Me.[Payee]) Then
Me.SubBillsChild.SourceObject = "subBillsMain"
Else
Select Case Me.Type
Case "Electricity"
Me.SubBillsChild.SourceObject = "subBillsElectricity"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Oil"
Me.SubBillsChild.SourceObject = "subBillsOil"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cable/Telephone/Internet"
Me.SubBillsChild.SourceObject = "subBillsCable"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cellphone"
Me.SubBillsChild.SourceObject = "subBillsCell"
DoCmd.ApplyFilter "subBillsNew", ""
Case Else
Me.SubBillsChild.SourceObject = "subBillsMain"
DoCmd.ApplyFilter "subBillsNew", ""
End Select
End If
 
S

songsungcn

Hello. I have been struggling with this project for over a week. I
think/hope I am almost done. I am using the below code on a main
form. I think everything is working properly except the apply
filter. I have one of 5 subforms open on a Form OnCurrent and "Type"
AfterUpdate. However, I want the apply filter to apply to what is in
the subform. I think it is applying to the Main form. I use the
subforms on several different forms, so I am trying to avoid having
the source for the subform be a filter based on the form that is open
because they all have different names (obviously).

Does anyone know how I can get the apply filter to work on the
subform?

Thank you very much.

If IsNull(Me.[Payee]) Then
Me.SubBillsChild.SourceObject = "subBillsMain"
Else
Select Case Me.Type
Case "Electricity"
Me.SubBillsChild.SourceObject = "subBillsElectricity"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Oil"
Me.SubBillsChild.SourceObject = "subBillsOil"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cable/Telephone/Internet"
Me.SubBillsChild.SourceObject = "subBillsCable"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cellphone"
Me.SubBillsChild.SourceObject = "subBillsCell"
DoCmd.ApplyFilter "subBillsNew", ""
Case Else
Me.SubBillsChild.SourceObject = "subBillsMain"
DoCmd.ApplyFilter "subBillsNew", ""
End Select
End If
Click to expand...

You should DoCmd.ApplyFilter on each subform's OnCurrent event!

Johnny Sung
 
N

nybaseball22

This is what I want to avoid because the filter is based on
information in the main form. That would mean I would have to have a
different subform for each Main form I place it in, wouldn't I? Is
there a way to avoid this?
 
M

Marshall Barton

Hello. I have been struggling with this project for over a week. I
think/hope I am almost done. I am using the below code on a main
form. I think everything is working properly except the apply
filter. I have one of 5 subforms open on a Form OnCurrent and "Type"
AfterUpdate. However, I want the apply filter to apply to what is in
the subform. I think it is applying to the Main form. I use the
subforms on several different forms, so I am trying to avoid having
the source for the subform be a filter based on the form that is open
because they all have different names (obviously).

Does anyone know how I can get the apply filter to work on the
subform?

Thank you very much.

If IsNull(Me.[Payee]) Then
Me.SubBillsChild.SourceObject = "subBillsMain"
Else
Select Case Me.Type
Case "Electricity"
Me.SubBillsChild.SourceObject = "subBillsElectricity"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Oil"
Me.SubBillsChild.SourceObject = "subBillsOil"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cable/Telephone/Internet"
Me.SubBillsChild.SourceObject = "subBillsCable"
DoCmd.ApplyFilter "subBillsNew", ""
Case "Cellphone"
Me.SubBillsChild.SourceObject = "subBillsCell"
DoCmd.ApplyFilter "subBillsNew", ""
Case Else
Me.SubBillsChild.SourceObject = "subBillsMain"
DoCmd.ApplyFilter "subBillsNew", ""
End Select
End If
Click to expand...


DoCmd is a clumsey way to do things if there is another way.
I think most of your problem is because DoCmd doesn't know
which form should be filtered and may apply it to the wrong
form.

More specifically, you are specifying the WhereCondition
argument as a zero length string and, according to Help that
overrides the Filter argument. Effectively, if ApplyFilter
can work for you, you can only specify one of the arguments.

You never explained why you are using the filter name
argument instead of the where condition argument so it's not
clear what you are really doing. If you can find a way to
use the where condition argument (instead of a query name),
then a more direct way to do what you want would be more
like:

If IsNull(Me.[Payee]) Then
Me.SubBillsChild.SourceObject = "subBillsMain"
Me.SubBillsChild.FilterOn = False
Else
Select Case Me.Type
Case "Electricity"
Me.SubBillsChild.SourceObject = "subBillsElectricity"
Case "Oil"
Me.SubBillsChild.SourceObject = "subBillsOil"
Case "Cable/Telephone/Internet"
Me.SubBillsChild.SourceObject = "subBillsCable"
Case "Cellphone"
Me.SubBillsChild.SourceObject = "subBillsCell"
Case Else
Me.SubBillsChild.SourceObject = "subBillsMain"
End Select
Me.SubBillsChild.Filter = "filter string"
Me.SubBillsChild.FilterOn = True
End If
 
N

nybaseball22

Thank you. I tried this code and it is not working. I'm sure I need
to change something, but I don't know what.

Here is what I want to do. When I open BillNew, the subform opens
with a blank sub. After I make a selection in Type, it opens a
subform using the code above. When this subform opens, I want it to
open to the correct information. I could add a query that would have
the subform open to the correct record, however all these subforms are
used on 3 forms. If I make the source for the subform a query with a
form string, I will need new subforms for each Main form, won't I?

Thank you.
 
M

Marshall Barton

Thank you. I tried this code and it is not working. I'm sure I need
to change something, but I don't know what.

Here is what I want to do. When I open BillNew, the subform opens
with a blank sub. After I make a selection in Type, it opens a
subform using the code above. When this subform opens, I want it to
open to the correct information. I could add a query that would have
the subform open to the correct record, however all these subforms are
used on 3 forms. If I make the source for the subform a query with a
form string, I will need new subforms for each Main form, won't I?
Click to expand...


I don't think there is a need to mess with queries and
subforms.

Since you didn't post what you tried (since that DoCmd
stuff), I can't even guess why the idea I posted didn't work
for you. I would also need to know something more specific
about what your fiter looks like.

Except for the case where payee is Null, I think another way
to approach this would be to use the LinkMaster/Child
properties.
 
S

songsungcn

Thank you. I tried this code and it is not working. I'm sure I need
to change something, but I don't know what.

Here is what I want to do. When I open BillNew, the subform opens
with a blank sub. After I make a selection in Type, it opens a
subform using the code above. When this subform opens, I want it to
open to the correct information. I could add a query that would have
the subform open to the correct record, however all these subforms are
used on 3 forms. If I make the source for the subform a query with a
form string, I will need new subforms for each Main form, won't I?

Thank you.
Click to expand...

I tested Marsh's program, it is correct. Problem maybe you didn't have
a correct "filter string", it should be like this:

Me.SubBillsChild.Filter = "tableName!fieldName like '*xxx*' "
Me.SubBillsChild.FilterOn = True

You'd better write a program as this:

Dim filterStr as string
filterStr= txtBox1.text & "!" & txtBox2.text & " like '*" &
txtBox3.text & "*'"

Me.SubBillsChild.Filter = filterStr
Me.SubBillsChild.FilterOn = True


Where txtBox1 is 1 of your 5 sourceObject, they are supposed table
names or query names.
txtBox2 is field name for different sourceObject
txtBox3 is filter string for different sourceObject

Johnny Sung
 
M

Marshall Barton

I tested Marsh's program, it is correct. Problem maybe you didn't have
a correct "filter string", it should be like this:

Me.SubBillsChild.Filter = "tableName!fieldName like '*xxx*' "
Me.SubBillsChild.FilterOn = True

You'd better write a program as this:

Dim filterStr as string
filterStr= txtBox1.text & "!" & txtBox2.text & " like '*" &
txtBox3.text & "*'"

Me.SubBillsChild.Filter = filterStr
Me.SubBillsChild.FilterOn = True


Where txtBox1 is 1 of your 5 sourceObject, they are supposed table
names or query names.
txtBox2 is field name for different sourceObject
txtBox3 is filter string for different sourceObject
Click to expand...

Johnny,

That is the right idea, but you should use the Value
property, not the Text property. The Text property is only
appropriate while the text box is being edited (e.g. in the
Change event).

It's also fairly unusual to need to specify the table name
and the table/field separator is the dot, not exclamation
(even though ! does work in many cases).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform problem 3
Apply filter to subform 1
Subform Filter 4
Apply value of a combo box as a filter 3
Open a form and apply a filter to the subform 2
Filter on a lisbox entry 3
where condition in a filter 1
applying a filter to a subform 2

Top