reposted- form with popup criteria

[odun]

Hello all, I have edited my question to be clearer as I realize some might not understand what I posted earlier.

I have a form with the source from a query.
I have about 15 fields in my query, but users of the query regulary use about 5 of the fields.

How can I design my form such that on clicking the form, a set of text boxes comes up with fields from the query.
I found a post that I could use something like this in my query but that I needed to create a button on the form that would run the query.
Here is an example of the criteria I put in one of the query fields:

[Forms]![Manager]![Vendor] or Forms![Manager]![Vendor] Is Null

The problem is, on opening the form, I get a Name# error on each of the fields that have criteria in them.

Also, is there a way that I don't need to use a comman button on the form form, just by doubleclicking it will bring up text boxes and then I can put in the criteria needed.

What happens if I don't fill in one of the text boxes, would it only return results when that field is blank?

Please help,urgent.

Thanks again.

 

[llkhoutx]

The problem is, on opening the form, I get a Name# error on each of the fields that have criteria in them.

The ControlSource property of the various controls on the formk, are not fields in the query.

What happens if I don't fill in one of the text boxes, would it only return results when that field is blank?

The field with a blank would test as such, however, your using two fields in the criteria. The second field would also test.

Also, is there a way that I don't need to use a comman button on the form form, just by doubleclicking it will bring up text boxes and then I can put in the criteria needed

The better way would be to use the argument field in to docmd.openform "YourFormName" instruction, or, opening the form, setting the called form's Filter property to the requisite criteria and the called form's FilterOn property to True, then requerying the form, or, programmatically modifying your query., or, - - - there's many ways to accomplish the requisite result.

The simple way is to adjust the OpenForm command argument.

 

[odun]

Hi,
Thanks for the response. I have fixed the Name# error I was getting, you're right, I change the query name and forgot to change the form source accordingly.

I don't quite understand your third point. I have about 5 field in my query that may be populated. On clicking the form, I want access to be able to prompt the user to fill in the criteria for these fields. e.g.

on clicking my main form, I want a text box or another form to come up with the followng fields that prompts the user to fill out with the following:

Name
Vendor #
Date
State
Received

If the user does not want to fill in any of the fields, then the form would bring up all the records. If the user fills out 3 of the fields, then access brings up the records that match those 3 fields.

Then the user can press "submit" and access executes the query and the results come up on the main form.
I know how to use a parameter query, but I don't want the user to be prompted 5 times to enter the information, I just want 1 prompt and the user can enter all the information at once.

Thanks to everyone who can take a shot at this.

 

[llkhoutx]

A simple way of doing it is to use a popup form to with the unbound fields on it; concantenate the non-Null fields on popup into a (criteria) SQL string.

Then on te popup being closed, I open the new form; set it FilterOn property to True, and its Filter property to the SQL string; then Requery the new form.

That way you don't have to modify the query. Note however, that the query runs twice, on FormOpen and on Requery.

A more sophisticated way is to use the popup to rebuild the query, each time prior to the new form being opened; there's no Requery. In that case, the query is only run once, which is obviously great if you have a large number of records.

 

[odun]

thanks, I will research how to build a popquery, requery e.t.c.

 

[llkhoutx]

A popup form, not a "popup query."