By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,846 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,846 IT Pros & Developers. It's quick & easy.

applying a filter to a form that has a subform

P: 43
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_Categories 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
Share this Question
Share on Google+
13 Replies

Expert 100+
P: 1,287
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

P: 43
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_Category_sub". The Source Object for the subform is the form "sub_Vendor_Category". The Link Child and Link Master fields are both "Vendor_ID".
3. On the form "sub_Vendor_Category", the Record Source is the "Vendor_Category" table which contains only "Vendor_ID" and "Vendor_Category" 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_Category_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

Expert 100+
P: 1,287
[Category] in the code should be replaced with the name of the field in the main form's underlying recordset.
Aug 27 '09 #4

P: 43
yes, did that. Here's my actual code

Me.Filter = "[vendor_category] = '" & Me.Vendor_Category_Filter & "'"
Me.FilterOn = True
Aug 27 '09 #5

Expert 100+
P: 1,287
If it's still asking for a parameter value, then "vendor_category" is not the name of a field in the recordset you are trying to filter.
Aug 27 '09 #6

P: 43
Agreed. The field name is definitely "vendor_category". 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_name" 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

Expert 100+
P: 1,287
To filter the subform, you would use:
subformControlName.Form.Filter = ...
Aug 27 '09 #8

P: 43
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.Filter = "[vendor_category] = '" & Vendor_Category_Filter & "'"
Vendor_Category_sub.Form.FilterOn = True
Aug 27 '09 #9

Expert 100+
P: 1,287
Make sure that Vendor_Category_sub is the name of the control on the main form.
Aug 27 '09 #10

P: 43

Well, it wasn't. Now it is. Now the filter works without an error, but the intended result isn't happening.

The sub-form now only shows the category chosen in the filter, but the main form still shows all records. Maybe I didn't explain properly, but I want to only see records on the main form that have a category matching the filter.
Aug 27 '09 #11

Expert 100+
P: 1,287
I see the problem. You can't filter the main form on the category field because it isn't in the vendor table, which is what the error said. See if you can do:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[VendorID] IN " _
  2.   & "(SELECT [VendorID] FROM VendorCategory WHERE " _
  3.   & "[category] = '" & cboVendorCategory & "')"
Aug 27 '09 #12

P: 43
beautiful !!!!

thanks so much!
Aug 27 '09 #13

Expert 100+
P: 1,287
Glad it works, and sorry I didn't read your initial post more carefully.
Aug 27 '09 #14

Post your reply

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