applying a filter to a form that has a subform

[bkberg05]

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.

Thanks,
Bill

[ChipR]

The Form.Filter Property is just like a Where Condition, so something like:

Me.Filter = "[Category] = '" & cboVendorCategory & "'"
Me.FilterOn = True

[bkberg05]

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.

[ChipR]

[Category] in the code should be replaced with the name of the field in the main form's underlying recordset.

[bkberg05]

yes, did that. Here's my actual code

Me.Filter = "[vendor_category] = '" & Me.Vendor_Category_Filter & "'"
Me.FilterOn = True

[ChipR]

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.

[bkberg05]

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.

[ChipR]

To filter the subform, you would use:
subformControlName.Form.Filter = ...

[bkberg05]

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

[ChipR]

Make sure that Vendor_Category_sub is the name of the control on the main form.

[bkberg05]

Thanks.

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.

[ChipR]

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:

Me.Filter = "[VendorID] IN " _
  & "(SELECT [VendorID] FROM VendorCategory WHERE " _
  & "[category] = '" & cboVendorCategory & "')"

[bkberg05]

beautiful !!!!

thanks so much!

[ChipR]

Glad it works, and sorry I didn't read your initial post more carefully.