Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter Subform using LIKE

Posted on 2013-12-26
7
Medium Priority
?
1,285 Views
Last Modified: 2013-12-31
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
0
Comment
Question by:dhemple
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 2000 total points
ID: 39740341
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
 

Author Comment

by:dhemple
ID: 39740361
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39740378
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 61

Expert Comment

by:mbizup
ID: 39740380
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
 

Accepted Solution

by:
dhemple earned 0 total points
ID: 39740424
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39740468
"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
 

Author Closing Comment

by:dhemple
ID: 39747879
Thanks for your help.  Your code helped me in solving the issue.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question