Don't able to filter subforms (1 Viewer)

[Rashid]

In ms access DB there is a navigation from with subforms I don't able to use "apply filter" in subforms. I am using where criteria as "Forms! NavigationF! Subforms! ItemId"
What mistake I am doing

 

[CJ_London]

With filters you need a comparison e.g.

filter=“somefield = “ & Forms! NavigationF! Subforms! ItemId

 

[Rashid]

i am using macro applyfilter on a button click event. in where field i am using "itemid=Forms! NavigationF! Subforms!searchbox

 

[Gasman]

I would expect you need a .Form in there after the subform control name?

 

[Pat Hartman]

Personally I never use filters but do they actually work on subforms? I thought there was some issue.
@Rashid If you can't get the filter to work, post back and I'll describe my method.

 

[Rashid]

Sir I am not able to filter the subform

 

[Josef P.]

i am using macro applyfilter on a button click event

Does it have to be a macro?
With VBA, if the button and searchbox is in subform:

With Me
    .Filter = "itemid=" & .searchbox.Value ' .. for numeric values
    .FilterOn = True
End With

 

[The_Doc_Man]

i am using macro applyfilter on a button click event. in where field i am using "itemid=Forms! NavigationF! Subforms!searchbox

Gasman raises an excellent point in post #4. In the reference string, you have used either one too many elements or one too few elements. My question is, where is searchbox located?

I'll explain. (The teacher in me just woke up...)

When you use the "bang" or "!" character you are telling Access (or at least implying) that the item to the LEFT of the bang either IS a collection or it HAS a collection as its default property, and the item to the right of the bang is the NAME of the thing you wanted to find IN that collection. In your case, Forms!NavigationF looks in Forms for an object called NavigationF - and this works because Forms IS a collection of form objects and in your case, one of them is named NavigationF. No problem to that point.

You are now on a form object and you repeat the "bang" character (as in Forms!NavigationF!Subforms). The default behavior on a form is to search the Controls collection on the form because that collection is the default property of a form. (Just like .Value is the default property of a text box.) Technically, that reference looks like Forms("NavigationF")!Controls("Subforms"). However, because Controls was the default property, you can skip it. So Forms!NavigationF!Subforms works OK if Subforms was the name of the sub-form control.

It is the next step where there is a problem. If the searchbox control is on NavigationF, it would appear in place of where you have Subforms and that would be the end of the reference string. But you didn't do that, so you must think searchbox isn't on NavigationF. The item named searchbox can't be an item in Subforms either, because that is a pre-defined control, type sub-form. You cannot define arbitrarily-named objects inside a sub-form control because its components and properties are rigidly defined by Access. In the reference string you used, there is no place for searchbox to be.

Here's the solution. Sub-forms are just ordinary forms that are contained in sub-form controls. Subforms appears to just be the name of a control. If that is ALL it is, then the next thing after Subforms probably should be Form - which is the property of a sub-form control that represents the form that was placed in that control. The control is NOT the form. The control CONTAINS the form.

If we use Form in it proper place, a reference of Forms!NavigationF!Subforms!Form!searchbox says to look at the form contained in Subforms and to search ITS default collection (which would again be Controls) for searchbox. If that control is in the form contained by Subforms, then your reference might would work as intended.

 

[Pat Hartman]

To use a filter, your form needs to be bound to the table or to a query with no criteria. Access loads the entire table and then filters it locally. Since most of my apps either start out with SQL Server BE's or are expected to be converted at some point, I design all my apps with good client/server techniques to minimize future conversion issues. This optimization can also help when the BE is Jet or ACE.

Add a control on the main form that you will use for filtering. It can be a combo or a textbox whichever makes sense. Sometimes you might need multiple controls.

Then change the RecordSource of the subform to be a query with a where clause:
Where SomeFK = Forms!mymainform!txtPK And Somefield = Forms!mymainform!txtSomeField

In the AfterUpdate event of the txtSomeField control, you need one line of code.
Me.sfrmYourSubform.Form.Requery

If you want the subform to be able to show all related records as an option, then use this:
Where SomeFK = Forms!mymainform!txtPK And (Somefield = Forms!mymainform!txtSomeField OR Forms!mymainform!txtSomeField Is Null)

Make sure to use the parentheses as shown. Without the parentheses, the logic will not work as intended.