473,883 Members | 1,619 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

applying a filter to a form that has a subform

43 New Member
Hi - I have a form called Vendor (tied to table with same name). Each vendor_id can belong to more than one 'category'. So there's a table called Vendor_Category which contains just vendor_id and category.

On the Vendor form, there's a subform called Vendor_Category . When a user is in the Vendor form and is in a record for a particular vendor_id, they use a combo_box in the Vendor_Category subform to select Vendor_Categori es that can belong to the Vendor in question.

That works fine, but now I want to filter by Vendor_Category and just see vendor records the belong to a certain category. So I added an unbound vendor_category combobox which just contains a list of all the different categories. When a user selects one from the list, I've added code in the AfterUpdate that I want to use to set the filter.

Unfortunately, I have no clue how to write the filter. I know I set the filter and then turn the filter on. But the syntax is a mystery to me.

Any help would be greatly appreciated.

Aug 27 '09 #1
13 2124
1,287 Recognized Expert Top Contributor
The Form.Filter Property is just like a Where Condition, so something like:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[Category] = '" & cboVendorCategory & "'"
  2. Me.FilterOn = True
Aug 27 '09 #2
43 New Member
Hi and thanks,

The part that's giving me trouble is the [Category] section in your example. When the event runs, it opens up a dialog box asking for the parameter value.

When I'm putting in the code and I put the field in as [category] with lower space, it doesn't recognize it and change it to uppercase. I thought maybe it was because it was on a subform, but even when I put in a field from the main form, it still doesn't change to upper case.

Note the following information:
1. Main form name is "Vendor". It's Record Source is the "Vendor" table. "Vendor_ID" is the primary key.
2. Sub_form name on the main form is called "Vendor_Categor y_sub". The Source Object for the subform is the form "sub_Vendor_Cat egory". The Link Child and Link Master fields are both "Vendor_ID" .
3. On the form "sub_Vendor_Cat egory", the Record Source is the "Vendor_Categor y" table which contains only "Vendor_ID" and "Vendor_Categor y" fields. The "Vendor Category" field is what I'm trying to filter on.
4. The combo box on the "Vendor" form that I'm using to trigger the filter is called "Vendor_Categor y_Filter".

Just wanted to be thorough. I've checked to see if the [vendor_category] name was duplicated somewhere, but I can't find another instance.

Thanks again for any future help.
Aug 27 '09 #3
1,287 Recognized Expert Top Contributor
[Category] in the code should be replaced with the name of the field in the main form's underlying recordset.
Aug 27 '09 #4
43 New Member
yes, did that. Here's my actual code

Me.Filter = "[vendor_category] = '" & Me.Vendor_Categ ory_Filter & "'"
Me.FilterOn = True
Aug 27 '09 #5
1,287 Recognized Expert Top Contributor
If it's still asking for a parameter value, then "vendor_categor y" is not the name of a field in the recordset you are trying to filter.
Aug 27 '09 #6
43 New Member
Agreed. The field name is definitely "vendor_categor y". But as it's on a sub-form, somehow it must not be part of the recordset? If I change the name to a field on my Vendor form such as "vendor_nam e" for instance, it recognizes it fine.

Either i'm referring to the field wrong (by not referencing the sub_form name) or the recordset needs to be expanded to include the data in the sub form and i don't know how to do that.
Aug 27 '09 #7
1,287 Recognized Expert Top Contributor
To filter the subform, you would use:
subformControlN ame.Form.Filter = ...
Aug 27 '09 #8
43 New Member
Here's what I wrote. It now says 'Object Required' in the debugger. Maybe I don't know how to reference the [vendor_category] field correctly as it is also in the subform...

Vendor_Category _sub.Form.Filte r = "[vendor_category] = '" & Vendor_Category _Filter & "'"
Vendor_Category _sub.Form.Filte rOn = True
Aug 27 '09 #9
1,287 Recognized Expert Top Contributor
Make sure that Vendor_Category _sub is the name of the control on the main form.
Aug 27 '09 #10

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

Similar topics

by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside from queries. Let me just add that Allen Browne excellent article about this subject may not apply to this scenario on an elementary level. (Here's the link to the article; Filter a Form on a Field in a Subform -...
by: damjanu | last post by:
Hi All; I need little help. I have a datasheet form. I allow user to do 'filter by selection'. My form contains a column with values. As user changes selections, I want to calculate totals. I can do this the first time the form loads.
by: cefrancke | last post by:
I have a form (no underlying record set) that has two separate sub-forms on it. Each sub-form has data from two different tables. Above each sub-form there is one unbound combo box with a SQL record source that returns an ID field and a Text field. Next to each combo box is two buttons, one that 'Applys' the filter of the sub-form to the value in the combo box and one that 'Clears' the form's current filter. When the click event of the...
by: MS | last post by:
I'm having trouble applying a filter to a subform. I create a String in a Module based on various selections on the form. Clicking a button on the "stand alone form" that changes the filter property string to the string from the module works fine. As soon as I put the form on the "main form" (thereby coverting it to a sub form) things no longer work. Of course the record source for the sub form is no longer a table or query, but the...
by: dhowell | last post by:
In reading some of the posts on this group, it appears as though it is not strait forward at all to filter a form, which has subforms, by criteria which are either on subforms or span more than one subform..... This MSDN article however makes it sound as simple as clicking "Filter by Form", entering the search criterial in the form and/or it's subforms, then just clicking "Apply Filter"...... ...
by: jcf378 | last post by:
Is it possible to set a main-form with an embedded subform to "Filter by Form", subsequently enter the desired variables in fields in BOTH the main-form and subform, and then save the resulting filter as a single query? or, do the variables entered on the main form need to be saved as its own individual query, and the variables entered on the subform saved as a separate query. I ask because I would ideally like to take a single query,...
by: jambonjamasb | last post by:
What I am trying to do is have a combo box which allows me to filter for a certain field in a subform. I can't think how to do this and have tried with the follwoing code. Basically the field in question is : POL_Change_Allocated - This is a yes/no tick box. I want to filter for all forms where the tick hasn't been applied. (Am I right in thinking that this means this field is no unless ticked?) The main form feeds from table OCP Base...
by: angi35 | last post by:
Hi - In Access 2000, I have a form I want to filter, but I can't get the syntax right in the code. Form: Subform: Control on : txtStart Nested Subform on : Control on : txtSDate
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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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: 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 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.