Apply Filter to Subform

[nybase...@gmail.com]

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

[songs...@gmail.com]

On Jan 20, 6:44 am, "nybasebal...@gmail.com" <nybasebal...@gmail.com>
wrote:

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

Johnny Sung

[nybase...@gmail.com]

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?

[Marshall Barton]

nybase...@gmail.com wrote:

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

--
Marsh
MVP [MS Access]

[nybase...@gmail.com]

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.

[Marshall Barton]

nybase...@gmail.com wrote:

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.

[songs...@gmail.com]

On Jan 20, 12:22 pm, "nybasebal...@gmail.com" <nybasebal...@gmail.com>
wrote:

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

[Marshall Barton]

songs...@gmail.com wrote:

>On Jan 20, 12:22 pm, "nybasebal...@gmail.com" <nybasebal...@gmail.com>
>wrote:
>> 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?
>>
>

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

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).