Query from an unbound form

[Guest]

I would like to create an unbound form that looks similar to my form,
Clients, which would allow users to enter criteria into textboxes and pull up
a query which shows only the records from tblClients that match that
criteria. I would like any textboxes that are left blank to not be used as
criteria for the query. That is, if all the textboxes on the undound form
were left blank every record from tblClients would show up. How can I do
this? Thanks!

 

[Armen Stein]

I would like to create an unbound form that looks similar to my form,
Clients, which would allow users to enter criteria into textboxes and pull up
a query which shows only the records from tblClients that match that
criteria. I would like any textboxes that are left blank to not be used as
criteria for the query. That is, if all the textboxes on the undound form
were left blank every record from tblClients would show up. How can I do
this? Thanks!

What you are describing is similar to the built-in "Filter by Form"
capability in Access. You can find info about it in Access Help, or try
the "funnel" icon in the toolbar.

If that isn't what you want, you can use VBA to build up a Where clause
that includes values from each field that has a value on your unbound
form. Then use it to change your RecordSource property on the bound
form. For each field that your user leaves blank, just skip it and
don't add anything to the Where clause. If the user leaves them ALL
blank, don't even add the Where clause at all.

If you want to see a simple example of this, look at the unbound report
selection form in the download "Report Selection Example" here:
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

 

[Guest]

I think the following may help. The criteria doesn't necessarily have to
dates of course.

http://www.databasedev.co.uk/report_date_parameters.html

I then realised why not just use the filter?

 

[Guest]

I have several dozen fields and I would like to know if, instead of writing
code for each one individually, there is some sort of shortcut. For instance
if I gave all my fields in the unbound form the same name as their
corresponding field on frmClients could I make a code that says If
IsNotNull([fieldname]) find records where Me.[fieldname] =
frmClients.fieldname. I realize this is not real code, but the point is that
this one line would check ALL the fields in the unbound form without having
to write code for each one. Thanks in advance for any help.

 

[Guest]

Just wanted to say that the reason I don't use a filter is because frmClients
contains subforms which I would like to include in the query, but cannot
include in a filter. Also I would like to be able to generate a report from
the query which also cannot be done with a filter. Thanks!