Searching a Subform

[goilev]

Hi everyone I hope you are all well!

I'm hoping for a little help…

I have a main form called frmRecipe with a linked subform called frmIngredients.

On frmRecipe I have an Ingredients search box (txtIngredientsSearch) with a search button (btnIngredientsSearch).

When the user enters an ingredient in the search box and clicks the search button the subform (frmIngredients) should filter to show matches of ingredients.

To achieve this, I wrote an 'ApplyFilter' macro for the search button’s click event, called mcoSearchIngredientsInRecipe - that looks like this:

Forms![frmRecipe]![frmIngredients]![Ingredient] Like "*" & Forms![frmRecipe]![txtIngredientsSearch] & "*"

When I click the search button (having entered an ingredient search term) no filtering seems to occur.

What am I doing wrong?

Thanks in anticipation!

 

[theDBguy]

Hi. Maybe you're missing a "Form" reference in your syntax. For example,

Forms!frmRecipe.frmIngredients.Form!Ingredient

 

[goilev]

Yes that reference was missing, thanks @theDBguy . However it doesn't seem to have solved my problem the issue persists.

 

[goilev]

To be clear my syntax is now:

Forms![frmRecipe]![frmIngredients]!Form![Ingredient] Like "*" & Forms![frmRecipe]!Form![txtIngredientsSearch] & "*"

 

[theDBguy]

To be clear my syntax is now:

Forms![frmRecipe]![frmIngredients]!Form![Ingredient] Like "*" & Forms![frmRecipe]!Form![txtIngredientsSearch] & "*"

Hi. Just as a test, please copy how I used both the ! and the . (period) in my post and let me know if it makes any difference.

 

[goilev]

Forms!frmRecipe.frmIngredients.Form!Ingredient Like "*" & Forms!frmRecipe.Form!txtIngredientsSearch & "*"

Same issue I'm afraid.

 

[theDBguy]

Forms!frmRecipe.frmIngredients.Form!Ingredient Like "*" & Forms!frmRecipe.Form!txtIngredientsSearch & "*"

Same issue I'm afraid.

Okay. Can you show us the complete ApplyFilter code? Thanks!

 

[goilev]

N.B. Access 2016 automatically inserts the square brackets on macro save.

 

[Deleted member 147267]

Forms!frmRecipe.frmIngredients.Form!Ingredient Like "*" & Forms!frmRecipe.txtIngredientsSearch & "*" ?

 

[goilev]

Unfortunately still no better. Thanks for your help so far both.

 

[Deleted member 147267]

Actually probably still not right. First, if the combo or textbox to filter by is on the main form, then when referring to that control the use of Form is incorrect hence what I suggested. Then, I suspect the control reference needs to be wrapped in quotes, so
Forms!frmRecipe!frmIngredients.Form!Ingredient Like "*'" & Forms!frmRecipe.txtIngredientsSearch & "'*"
Note: I also replaced the . after form name with ! because that's how I learned to do it. Not sure if it matters.

 

[Deleted member 147267]

If that's not right, post the vba where you're applying the filter.
EDIT - never mind; I see that it's a macro. If the single quotes don't help, substitute the second reference with an actual text value and get that working first.

 

[goilev]

I have made those changes and now further curious behaviour... the main form now blanks itself when I click the search button.

 

[goilev]

I've stripped this right back to:

[Forms]![frmRecipe]![frmIngredients].[Form]![Ingredient]="Salt"

Same behaviour as before.

 

[theDBguy]

View attachment 81736
N.B. Access 2016 automatically inserts the square brackets on macro save.

Hi. I think the ApplyFilter action applies the filter to the current (main) form. Just as a test, try moving the button to the subform and adjust the syntax to remove the reference to the main form.

 

[goilev]

Unfortunately the subform is a Datasheet so moving the button there is not possible as far as I'm aware(?)

 

[goilev]

Perhaps a macro is not the way to go here? Does anyone have the VBA syntax that might work here?

 

[theDBguy]

Perhaps a macro is not the way to go here? Does anyone have the VBA syntax that might work here?

Okay, you could try this:

Me.frmIngredients.Form.Filter="Ingredient Like '*" & Me.txtIngredientsSearch & "*'"
Me.frmIngredients.Form.FilterOn=True

 

[goilev]

Thanks so much everyone for you help! It really is appreciated on a Saturday night!

@theDBguy your VBA syntax worked a treat.

We beginners are so lucky to have a resource like this forum with such patient and generous members.

Have a wonderful weekend all and please all stay safe!

 

[Cronk]

Is frmIngredients the name of the subform control in frmRecipe? Or is it something like Child5?