Filter Subform using LIKE

Hello Experts.

Is it possible to filter a subform using a LIKE statement that refereneces a text box on the form?  If yes, could you help with my code?

Thank you.

Me.subForm.Form.Filter = "Like '*' & Me.subForm.Form![txtbox] & '*' "
Me.subForm.Form.FilterOn = True
dhempleAsked:
Who is Participating?
 
dhempleConnect With a Mentor Author Commented:
The code you provided gave me the same error.


I found a solution..  for whatever reason the code did not like the use of "Me."  I had to use the actual Main Form name to get it to work.

Working code:
Forms![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData]![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form.Filter = "[IPD_New_BrandName] Like '*" & Forms![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData]![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form.[txtSearchBrand] & "*' "
Forms![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData]![frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form.FilterOn = True

Summary of working Code:
Forms![MainForm]![SubForm].Form.Filter = "[Field To Filter On] Like '*" & Forms![MainForm]![SubForm].Form.[txtbox] & "*' "

Forms![MainForm]![SubForm].Form.FilterOn = True
0
 
mbizupConnect With a Mentor Commented:
You need to specify a field name and correct the quotes:


Me.subForm.Form.Filter = "[TheFieldIWantToFilter] Like '*" & Me.subForm.Form.[txtbox] & "*' "

Open in new window

0
 
dhempleAuthor Commented:
Thank you for your response.

I made the changes to my orginal code as you have exampled.  I'm getting a Run-time error '2465': Microstoft Access can't find the field '|1' referred to in your expression.

Any idea why this is occuring?

Orginal Code:
Me.[frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form.Filter = "[IPD_New_BrandName] Like '*" & Me.[frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form.[txtSearchBrand] & "*' "
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mbizupCommented:
Try this variation:

With Me.[frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData_SubFrm].Form
           .Filter = "[IPD_New_BrandName] Like '*" &  .txtSearchBrand & "*'"
           .FilterOn = True
End with

Open in new window


And post back with any errors.
0
 
mbizupCommented:
As an aside, your naming conventions *might* be problematic.  Best practice is to avoid spaces and special characters in your variable and object names.
0
 
mbizupCommented:
"Me"  means the 'current form'.

If the code was located somewhere on frm_PremierProdLaunchPlanW/OMatchingtbl_IPD_NewData, then 'Me' would work.

If it was located elsewhere, such as a function in a public module, then the full form reference, as in your posted solution, is required.
0
 
dhempleAuthor Commented:
Thanks for your help.  Your code helped me in solving the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.