OpenForm filter by Query

[kvar]

I am using a form (I call it a preform) that users select or enter criteria in, the click a button, and another form opens with records that were filtered by a query based on the criteria they entered.

The query seems to work just fine. If I just open the query, and type parameters into the prompts that pop up I get what I should.

However, if I put those same parameters into the "preform", then click the button, I get all the records, no filter.

Here is the weird part, the preform and button work like they are supposed to for awhile, lets say 10 or 15 different searches, then all of a sudden it just doesn't work any more and returns all the records. No matter what parameters I put in.

In the On Click event for the button I have:
DoCmd.OpenForm "NewWorkfrm", acNormal, "Query1"

Am I missing something stupid??
This is making me nuts, so ANY help is greatly appreciated!!

 

[MStef]

As I can understand you, you don't need 2 forms for this.
It's enouth 1 form.
Look at "DemoFilterFormCA2000.mdb" (attachment, zip).
Open Form and see.

 

[kvar]

I can see how that would work nicely if I was just searching by 1 or maybe 2 simple criteria.
But I have 7 different criteria that can be searched by, combined or individually. Several of them use the Like "*" operator to search for strings.
Plus, it's a split database, it defeats the purpose if the user still has to have all the records loading on the front end. There are so many records that would slow things down immensely.

According to the Help files, and everything I can find, the OpenForm Cmd should be perfectly capable of using a query as a filter. It even pops up in the syntax in VB when you start typing it!

 

[MStef]

Ok, in that case I suggest to use FILTER BY FORM, APPLY FILTER, REMOVE FILTER, CLEAR GRID (icons).

 

[kvar]

Filter by Form doesn't allow the search capabilities that a Query does.
If I put the text "Valley" in the Ship To Name field of the form as a filter it returns no results.
But if I put it in the query, it does.
Filter by Form doesn't search strings within a field. You can't use the Like "*" operator in it.

 

[MStef]

Yes, Filter by form can use *, and can all as a query.
Use a CLEAR GRID icon, because the filter remember all from last using a filter by form.

 

[DKO]

Your code is fine.

Post your query. It sounds like some kind of data type mismatch. Remember text values must be enclosed in parentheses, dates in "#" signs, and numbers don't need either.

Also pay close attention to where you have your "OR"s and "AND"s...

 

[kvar]

First..Thank you, thank you, thank you, for all the assistance!
I guess/think I figured it out.
I emailed the database to myself to look at it at home. I have 2007 at home, 2003 at work.
2007 is apparently smarter, it added some things on it's own. And now everything seems to be fine, doesn't break after a LOT of searches, opens, closes, etc.
What I noticed, is that in the Properties of the Main work form (the one that opens after the search) It changed the Record source to the Query that was being run off the Preform (search form). And changed the Filter on Load to "Yes". And for the Filter it put in the text of the query.

When I think about this, it seems logical that it would work.
Especially when you consider that just running the query, and typing the Parameters into the prompts........everything already/always worked perfectly.
It was just when running it from the form that i had issues.

I don't know.....seem logical to you?