Filter with multiple criteria

[dominicgingras]

I have a Continuous Form that display products information.
On the top of the form I have different combo box to filter the list of my product.

I have been trying to use different method to achieve my goal but I got different problems.

Does someone have a "best approach" solution to this?

me.filter = ... or docmd.applyfilter or changing to record source property...

Help please!

 

[Sorrells]

dominicgingras,

I am guessing here but it sounds like you have a main form with a subform. The main form is perhaps tied to a Products Table whereas the subform (Continuous in display type) is tied to a Products Detail table. There is some field relating the 2 tables, say ProductID. There is a one to many relationship.

I further guess that you want to see the detail information in the Continuos subform for any Product you select from the combo box.

Let me know how close I am to describing your form environment.
Regards, Sorrells

 

[dominicgingras]

This form purpose is to browse the list of all the products and then go to a details form with more info about the product using a button. Both forms are based on the same table: Product. Their are so many products that I need the user to filter them by category, subcategory, type... And once he has Chosen the product go to a detail form.

You were very close except for the two tables...

 

[Sorrells]

dominicgingras,

My first thought is that your table is not normalized. There are a lot of ways of describing normalization but in the final analysis, if you are repeating a lot of information in a table, then it is not normalized.

If the products are in categories, for any category is there common information that is being repeated? That could be perhaps stashed in a second table related by category number? Same with subcategories. Take a look and see. You may be able to reduce the current amount of records and future data input.

Also could you provide more specifics concerning your comment "I have been trying to use different method to achieve my goal but I got different problems."

I will try to get back to this tomorrow. I'll have to make a test case but would think that even with a single table, a form-subform relationship could be created.
Regards, Sorrells

 

[dominicgingras]

I do have differents table for categories and sub-categories. I have more then a 2000 products and all the genreal info is stored in one table and related info is stored in other table.. But anyway this is not my point here.

I am trying to display a continous form that will show all the product to my users and then allow them to filter down their search. I have done it several time before using a

me.filter= xxx
filteron=true

But this time I have multiple criteria for filtering my result that I want to add on the top of each other.

So my question is what method people are using to filter a form using multiple criteria?

 

[Sorrells]

dominicgingras,

I am beginning to feel that you may have been better off if someone else had responded! I was just playing with a form with a dynaset display and reviewed the DefaultView property and what a Continuous Form looks like. I then took a look at the Filter keyword and ApplyFilter method in Help.

Now that I understand better your question, I feel even more sure that I should not have responded. I am not experienced in either of these. The good news for me is that these are tools worth keeping in one's kit. I'll leverage the knowledge gained here. As for you, I'm taking a look in 2 books and will at least provide some information that may have some value:

Access97 Expert Solutions by Stan Lexzynski
=> Use saves queries for RecordSources and RowSource properties as they are essentially compiled whereas an SQL statement "must be re-parsed and re-optimized each time the form or control calls it."
=> Separate primary from ancillary data. Include in the initial view only the fields the user needs access to most frequently.
=> "The Get form is arranged to help he user find records by applying the most convenient criteria information. The user can enter one or more criteria values and receive a filtered list of matches." [This sounds like your form.]
=> Simple Filtration: The user enters filter criteria into controls by typing or selecting values, then uses command buttons to apply and remove filters.
=> In his examples Stan uses SQL phrases or clauses to perform sorting [ORDER BY] and filtering [WHERE]. In his example code he uses 'Me!txtFilter", Me![formname].RowSource. [seems like a conflict with the 1sstbullet above.]
=> 'A second [favored] alternative for locating a specific record in a form's recordset using VBA code is to use the FindFirst method. Because FindFirst is a recordset operation, you must apply it to the form's recordset directly (via its clone).

Access97 Secrets by 3 authors:

In filtering, they note use of the ApplyFilter for VBA coding. They do not explicitly state a preference but this is the only approach they mention. If one of the two optional arguments is 'a filter name' that can be a saved query, this may be most efficient approach.

Again, I apologize for mis-understanding your question and diverting this post. I'll not reply in this thread again unless you specifically ask me a question. Good luck!
Regards, Sorrells

 

[PassingBy]

This might help you apply a filter on 2 combo boxes / 2 fields on your form.

DoCmd.ApplyFilter "", "[field1] = '" & Me.Cmbox1 & "' and [field2]= '" & Me.Cmbox2 & "'"

HTH

Martin

 

[dominicgingras]

Hi Sorrels!

Thank you for taking time to help people anyway. Some of what you said in your last post is making sense. I have try to use the docmd.applyfilter with a save query as a criteria.

I also have try to use "passsingby" method but I have forgot to mention that I want the criteria to return a wildcard (*) if the user leave the combo box empty.

Thread705-155413 is also showing a method to do this but I am getting error sometimes and I haven’t figure out why I am getting those error message.


Maybe "passingby" have an idea?