Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 10th, 2004, 03:46 PM
Registered User
Join Date: Mar 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Filters filters and again ....

I have a form with Orders and within it a Subform with the
Products of each.

How can I programmatically create a Filter based on a Product
Name? I want to see only the orders which contain a
specified product.

The Form's RecordSource does not contain the Product's name or code

Please help me out.

Old July 12th, 2004, 08:29 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

Seems to me your form and subform are backwards. Your form should have products and your subform should have orders based on those products. When you set up your parent/child relationship, your subform will filter automatically.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old July 16th, 2004, 01:18 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts

I agree with Greg,
you could have something like this in a command button on your subform

Form_MainForm.Filter = "((YourTable.ProductName=""" & Me.ProductName & """))"
Form_MainForm.FilterOn = True

I am a loud man with a very large hat. This means I am in charge
Old July 27th, 2004, 12:40 AM
Authorized User
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts

I agree & don't agree with Greg. Yes, normally the main form, by virtue of its Parent/Child relationship, "filters" the subform, usually on its foreign key.
But what about, when invariably, you get records, in this case products, that occur in more than one Parent record, (regardless of which form, is parent). Is it possible to "reverse" the filter (so to speak), for whatever reason?

Normally a viable suggestion Steve, but the subform does not contain any corresponding fields as the main form. The only commonality would be the linked fields, primary key & foreign key, I suppose.

If you filter the subform, hypothetically, with a combobox selection, due to the link, would the main form be filterd accordingly? I doubt it.(I'm thinking out loud here)

Here's my thought, pick a product, create recordset with that product only. Loop through, populating an array with all the foreign keys, that belong to that product. With array, create SQL, that will be appended to recordsource of main form.
All orders with that product (and a few others, I assume, if more than 1 product for an order), will be viewed.

If I've understood the post correctly, I think this may be an option.

Either way, good luck GregoryHu, it sounds like an interesting challenge.

Old July 28th, 2004, 10:59 PM
Authorized User
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts

GregoryHu, you're talking about a many to many relationship. You need an intermediary table for this.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameters / Filters s15199d Reporting Services 0 March 23rd, 2006 12:08 PM
About filters alf.guzman Servlets 0 December 16th, 2004 06:07 PM
logins/filters desireemm SQL Language 1 August 17th, 2004 05:53 PM
ISAPI filters k2k Visual C++ 0 July 10th, 2004 12:19 AM
Filters and Images. Nasimov HTML Code Clinic 1 November 18th, 2003 12:57 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.