Main/Sub forms and queries. I feel I'm missing something!

[Plumes]

Hi All

First I must thank Blorf for pervious help.

I am relatively new to the world of access and am currently building a database which has several main forms with subforms for data searches.

I have been using combo and text boxes in the main forms and linking them to the criteria in the queries on which the sub forms are based. Setting the criteria to:

Like "*"&[Control on main form]&"*"

This allows me to enter a null value and return all records. For date searches I've used first and last dates and set the default values to a date predating all records and current date Date().

This all works ok but has it's limitations. I have seen a database elsewhere that has forms with blank text boxes for searching records in the form below. If left blank all records are returned. The related queries have nothing in the criteria and there don't appear to be any sub forms. Unfortunately the forms are locked so I can't see how this works. Is it all in code somewhere?

As I said before I am relatively new to Access and feel I am missing a better more efficient way performing these searches.

Please help, any advice is welcome.

 

[SeeThru]

have a look at mvps.org/access

There is a usefull bit about adding 'all' to a combobox used as a query or report criteria.

You can also use the UNION command in sql to create the combo box list and add ALL to it, with the primary value as * or Not Null or whatever.