473,883 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Subform filter not working

54 New Member
I have a combo box with a list a values that when picked the form should filter to show the corresponding record from a table. Everything works fine when I open up the form by itself. However, I want this form to be a subform but when I put it in another form the filters quit working. Here is the code for the filter.

Expand|Select|Wrap|Line Numbers
  1. Private Sub finditem_AfterUpdate()
  2. Dim strFilter As String, strOldFilter As String
  3.     strOldFilter = Me.Filter
  4.     If Me!finditem > "" Then _
  5.         strFilter = strFilter & _
  6.                     " AND ([Chemical] Like '" & _
  7.                     Me!finditem & "*')"
  8.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  9.     If strFilter <> strOldFilter Then
  10.         Me.Filter = strFilter
  11.         Me.FilterOn = (strFilter > "")
  12.     End If
  13. End Sub
Aug 27 '12 #1
18 8043
5,501 Recognized Expert Moderator Expert

Replace the "Me" identifiers with the form's name (lines 3, 7, 10, etc...)

You'll find that forms that work fine by themselves with a Me reference will often break when used as a subform. I've found that replaceing the Me with the form's actual name often fixes this issue.

Aug 27 '12 #2
54 New Member
I changed the code to
Expand|Select|Wrap|Line Numbers
  1. Private Sub finditem_AfterUpdate()
  2. Dim strFilter As String, strOldFilter As String
  3.     strOldFilter = Me.Filter
  4.     If Forms!orderhistory!finditem > "" Then _
  5.         strFilter = strFilter & _
  6.                     " AND ([Chemical] Like '" & _
  7.                     Forms!orderhistory!finditem & "*')"
  8.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  9.     If strFilter <> strOldFilter Then
  10.         Forms!orderhistory.Filter = strFilter
  11.         Forms!orderhistory.FilterOn = (strFilter > "")
  12.     End If
  13. End Sub
This doesn't seem to work. It says that it cannot find the form. I have this subform in another form with the exact same code I gave first and it works. Just this one doesn't work.
Aug 27 '12 #3
ariful alam
185 New Member
you can embed a macro having "requery" action in the "onchange" event of the combo box. this will requery the records of the sub-form every time when you change the value of the combo box.
Aug 27 '12 #4
32,584 Recognized Expert Moderator MVP
Are you changing the form that is being filtered? Me only refers to the form that is associated with the module the code is running within. If you're referring to any other form then Me will simply be a wrong reference.

Otherwise, the Me reference should work fine.
Aug 27 '12 #5
54 New Member
It is the subform that is being filtered. It just happens to be viewed from within another form so me should work. Like I said if I open the subform by itself then the filter works, but if I open the main form to view the subform then the filter no longer works.

This is actually happening with both subforms on this main form.
Aug 27 '12 #6
32,584 Recognized Expert Moderator MVP

What are you referring to? It doesn't appear to be anything in this thread.

I hope you're not encouraging the use of macros in an Access project? That is a very clumsy approach, and it certainly doesn't fit this question. MS push it because it makes their lives easier. There is no discernable reason why any expert should ever encourage such an approach, which is known to cause Access projects so many unnecessary problems (It is, after all, a severeley restricted approach to logic expression and development).
Aug 27 '12 #7
32,584 Recognized Expert Moderator MVP
It is the subform that is being filtered. It just happens to be viewed from within another form so me should work.
You fail to make it clear where the code is housed. That is the crux of the point I was making and I cannot proceed without an understanding of the situation, I'm afraid.
Aug 27 '12 #8
54 New Member
I have frmorder where users can go to place an order. On that same form is a subform "orderhisto ry" where they can scroll through past orders as they create new ones on the main form. On the subform they choose an item from a combo box. In the after update event on the combo box on the subform I have the following code.
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String, strOldFilter As String
  2.     strOldFilter = Me.Filter
  3.     If Me!finditem > "" Then _
  4.         strFilter = strFilter & _
  5.                     " AND ([Chemical] Like '" & _
  6.                     Me!finditem & "*')"
  7.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  8.     If strFilter <> strOldFilter Then
  9.         Me.Filter = strFilter
  10.         Me.FilterOn = (strFilter > "")
  11.     End If
This code should filter the subform so that as they scroll through old records they only see the records for the item they selected.

If I open the subform by itself then the filter works. If I open the main form and then use the combo box and filter in the subform it no longer works. The combo box and code both lie within the subform. Everything that the code would effect lies within the subform. I apologize for me explaining skills. I hope this makes things more clear. Thanks.
Aug 27 '12 #9
32,584 Recognized Expert Moderator MVP
That's now Crystal Becker.

I'm surprised only by the facts as you describe them. I would not expect this code to work any less effectively when run from a subform than it would when run from a main form. If it had references to the Forms collection then that wouldn't surprise me, but references to Me always refer to the associated form, and the form will have Filter and FilterOn properties both when run as a Main Form as well as a Sub-Form. I'm afraid I see nothing that explains the behaviour you describe. In your position I'd be interested in seeing exactly what's happening where. Not something I can easily help with from a distance though, I'm afraid.

PS. I don't imagine something as fundamental as this is version dependent. I use 2003 but I doubt later versions would change something as fundamental as this.
Aug 28 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Scott | last post by:
In my database, I have a form with a subform. I had everything working correctly - the correct records would display on the subform for each record on the form. I could edit, add, and delete on the subform. I added a field to my table, and modified the design of my subform. Now, data is not getting displayed on the subform for any form records. I did not change any properties. I'm on XP Pro and using MS Office 2000 Pro.
by: Robert | last post by:
I have a form (Worksheet) that works fine by itself. I have now created a seperate form (MainForm) that has a command button in the header and an unbound subform (FormFrame) in the Detail section. when the user clicks the command button, Worksheet is loaded into the subform control. At this point, some of the functions do not work. The user is prompted to enter a Parameter Value. Why wouldn't that form (Worksheet) work the same as a sub...
by: theBman | last post by:
I have 2 tables. One master, one child using Access 2003 (PC) Have 2 forms. The main form is continuous while the sub is datasheet. I can filter on the main form and the subform filters with it. No problem. My goal is to be able to filter on the subform and have the main form filtered with it. Thought it would be simple but I for a week solid I have not been able to solve this on my own. I have review a zillion websites and still...
by: natwong | last post by:
Hi All, I'm a newbie in terms of Access and some of its functionality . I've been stuck on this problem for a couple days, even after searching the Web, etc. Currently I have five combo boxes (actually list boxes) that are multiselects in my main form. I need to use these combo boxes to filter a subform within my main form. My combo boxes are as follows: 1. A - 4 select options 2. B - 10 select options 3. C - 4 select options
by: Gilberto | last post by:
Hello, I have a form with some code to HIDE textboxes and labels when information is not used. I need this information in a report so i added the form as a Subform/Subreport in a report. The problem is that when runing the REPORT, it seems like the SUBFORM CODE DOESNT WORK, as it displays EVERYTHING. Its not following the code to HIDE the textboxes and labels, which are correctly hidden when running the FORM on its own. Whats happening,...
by: maciejfr | last post by:
Hello everyone, My problem is updating Subform filter by onchange event in one of main form field I've got following: Me!.Form.Filter = " = "" N "" OR ( = ""T"" AND notanr = " & Me!nrnoty & ")" Forms!frmNOTAWY.Form!.Requery
by: mrubel99 | last post by:
I have a subform built into a form in Access 2007. I would like the user to be able to filter the subform using their own criteria by right clicking the column header in the subform. Once the user has the subform record set filtered as they want I would like them to be able to click a button on the main form that will set the yes/no field in the filtered subfom data set to true. Here is the code I have been trying to get to work and it...
by: diogenes | last post by:
"Rick Brandt" <rickbrandt2@hotmail.comwrote in news:bPnKj.456$%41.325 @nlpi064.nbdc.sbc.com: I used this approach, and it works a treat! ID In(SELECT Order_ID FROM orderitems WHERE NAME = 'product') I've not used an In clause before. Thanks a lot for the education.
by: WyvsEyeView | last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet subform and it is not working. After making the selection in the first combo box, when I move to the second combo box, I get the Access input parameters box. If I input the value that is in the first combo box, I get the expected results in the second...
by: rwalle | last post by:
Hi : I have done a form with a subform inside to filter customers, the form have a Text box where user write down a name then I take this text box data as a criteria to the query wich subform data is tied to, then using a click event in a form button I run requery Comand and this way I can filter data to be showed, so far it works , now I want to add 2 more filter criteria TextBoxes to the same form in order to make it flexible so when no...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.