Multiple Queries from a Form and return resultd to that Form

[Sam40mUK]

Evening,
I am venturing into Access after a 7 year absence so excuse me if I appear somewhat slow!
I have created a User Form and I now wish to Query the data possibly contained in that Form.
I would like to be able to put the Criteria or Multiple Criteria in a user friendly Form and then by a macro button run the Query and Output to the same form .... is this possible or is there an easier way?
Any advice or help appreciated.
Thanking you in advance
Simon

 

[Corticus]

Hi Sam40mUK,

If you open your query in design view, and right click on the criteria field you're interested in, you can select 'build', and use the expression builder to find the object on your form that will hold this criteria. Add 'Is Null' to the 'or' field to account for no criteria entered.

For multiple criteria, you will have to adapt this approach a bit. Access won't let you have multiple 'is null' conditions (to my knowledge), so the easiest way to accomodate this, if you don't have too many criteria to deal with, is to query a query. In other words, have your first query with one criteria field pulled from the form, with 'or is null'. Then query this query, and add criteria to the next field, with 'or is null', and so on.

If you will have lots of different potential criteria, one way to deal with this is to build an SQL expression based on what criteria has been entered, and what hasn't.

If you set the form's data source to the query, you won't have to do anything to 'run' the query, just add a Me.Requery to the 'on_change' events for each object that will hold criteria. You might want to add a listbox that looks to this query, and have the wizard have the listbox select a record based on what you've selected in the listbox, then add 'listboxname.requery' to the listbox on_change event to keep it's contents fresh.

HTH,

 

[jfgambit]

Actually, you can use multiple Null values, but you will need to pass theses along to the Query utilizing the QueryDef function.

If you would like an example of a search database, let me know and i will forward it on to you.

 

[Corticus]

I would like an example,

it doesn't do us much good to tell us you know how to do something and then keep quiet...would you care to elaborate?

I did some more searching on this, and the Access kb speaks some on querydefs, without much elaboration on using them to accomodate for null values...

I did find this nice example that constructs an SQL expression, adding criteria to the SQL string for each field in the form, if it is not blank. It is from:
http://www.access-programmers.co.uk/forums/
not my design, though I like it.

here's the file:
http://theillumni.com/posts/multicriteria.zip

HTH,