Filter form based on multiple criteria

[Jack Cowley]

Create your form that will be the subform. (It can be datasheet view or whatever you like.) Use the query that gets its criteria from your other form as the Record Source for the form that will be the subform. That is it. If the subform is going to be on the form that has the criteria then in your button or however you trigger your code, add a Me.SubformName.Form.Requery or you may have to use Me.SubFormName.Form.RecordSource = "NameOfYourQueryHere".

hth,
Jack

 

[Zako]

Ok.. I am narrowing this down finally... but still I have some problems.

In the Query it is doing something strange...

I put the following in "Criteria" for the field called "OrderNumber"

Like Forms![frmSearch]![OrderNumber] & "*" Or Forms![frmSearch]![OrderNumber] Is Null

(its all one line in access)

I run the query, it pops up a box asking for each criteria for the 6 fields I have made searchable. (which it shouldnt do)

I go back into design view of the query, it has removed everything after the "*"

I tried adding the stuff after "Or" to the 2nd line in Criteria that is labeled "Or:" but it ends up removing that also.

Is there something wrong with my query that causes this?

If I run the form that attaches to this query directly, I can enter a criteria in any one of the boxes and hit search, and have it bring up the proper data. but there is a problem...

This form needs to be inserted into a "Main Form" that has tabs. One of the tabs will house the "Search Form." The problem is... when I open that main form I get popup boxes asking for criteria in that query and if I go to the search tab I cannot get the Query to work.

I know that is long winded...

Basically what I would like to happen is have the subform not attempt to run its query unless the user hits the button to run it. I suspect part of the problem is the fact that it is removing that part of my query string for some reason.

 

[Jack Cowley]

I realize that the method you selected (Query By Form) was easier on first blush but now you are running into some of its limitations. The code you want should have this line in the Criteria line:

Like Forms![frmSearch]![OrderNumber] & "*"

Then in a blank column you want this on the Field (top) line:

Forms![frmSearch]![OrderNumber]

and in the Or criteria line of this column you want Is Null. (By the Or criteria line I mean that the Is Null is to be on line below the Like criteria.)

The problem with this is that it will probably mess up the other criteria.

I would suggest that you abandon ship, go take a look at the QBF using a saved QueryDef article and spend your time sorting it out as it is much more flexible and will stand you in good stead as you continue to use Access.

hth,
Jack

 

[Zako]

Ok, I did as you suggested and went back to the other one...

I have so far made 3 fields searchable. With this new way of doing it, will it be possilble to make the cmdButton requery the Subform instead of opening a new query window?

 

[Jack Cowley]

Yes. Substitute the line of code that opens the query with this line of code:

Me.SubformName.Form.RecordSource = "Dynamic_Query"

That should do the trick....Be sure and use the name of your subform control where it says, 'SubformName'.

Jack

 

[Zako]

Hmm, when I do that, I see the status say "calculating" briefly but no records appear in the subform.

I know the criteria I am searching for is good, since if I leave the original code in to open the query I get about 12 records

 

[Jack Cowley]

Since you have run your code there should be a query named "Dynamic_Query" in your list of queries. Click on it and open it and see if there is data there. If not, then you have a problem in your code. If the data is there then you have a problem with the form that is based on the "Dynamic_Query".

Now you will have to fix one or the other...

Jack

 

[Zako]

The query does exist... "Dynamic_Query" so that is ok..

If I open the form (sbfrmNewSearch) based on that query I do get it populated with the last query results.

If I open the query form that has a subform of (sbfrmNewSearch) the data does not populate.

I have been having some trouble making subforms do their data population properly and I am unsure why. I assume that since the form works fine when directly opened, that it should also populate when accessed as a subform.

here is the command I put in the code...

Me.sbfrmNewSearchObj.Form.RecordSource = "Dynamic_Query"


the name of the query form is "frmNewSearch"
the name of the data form is "sbfrmNewSearch"
the name of the subform object in the "frmNewSearch" is "sbfrmNewSearchObj"

 

[Jack Cowley]

A subform is another control on a from just like a combo box or a text box. Be sure you are referring to the control in your code not the name of the subform. Your code now reads:

Me.sbfrmNewSearchObj.Form.RecordSource = "Dynamic_Query"

Is the name of the control sbfrmNewSearchObj? To check this open you main form in design view. Click on the subform so that you see the little square black boxes on the corners and side. Open the property sheet if it is not already open. The property sheet had a blue bar across the top and in there it says:

Subform/Subreport:xxxxx

Where I have put the x's is the name of the control if it is NOT sbfrmNewSearchObj then replace sbfrmNewSearchObj with the name you found. Now it should work.

hth,
Jack

 

[Zako]

Checked that, and I had it correct. I cannot figure out what is wrong with this thing.

For some odd reason, when I open the actual "result form" it works fine but when it is opened as a subform of the "query form" it does not get the data.

Its almost like the command i listed below is not actually doing what it should when it is on the "query form"

Me.sbfrmNewSearchObj.Form.RecordSource = "Dynamic_Query"

If I make a button on the actual "result form" with the following command it does requery the data. However, it does NOT work when it is opened as a subform. *boggle*

Me.Form.RecordSource = "Dynamic_Query"


I wish I could just drop the whole thing and tell the users we cant do this... but at this point it is really bugging me...


I do appreciate all your help. If you can think of a way to get this subform to respond let me know.


Thanks,
Jason

 

[Jack Cowley]

Jason -

What version of Access are you using? If you have Access2000 and will email me your email address I will email you a working sample of what I have been talking about.

Jack

 

[Zako]

I sent you and email just now using the forum 'send email' feature, hopefully you got it.