Complex User Input Form

[Kitty]

I need to have a form where the user will do a combination
of selecting items from lists and inputting other items.
I then need to take what the user has selected/input and
use it as query parameters.

For example, the user would be able to select multiple
product types from a list box, input a dollar amount,
input a couple of different types of dates and pick
another classification code from a list box. To
illustrate, the user could want a list of all accounts
using dairy or grain products with total sales over $XX
since YY date with a credit class of A. Or they may want
just all dairy and grain customers regardless of size,
date or credit quality.

Help! Would it be best to dump the results of what the
user selects into a table and use that as parameters in
the query? Should I capture the information some how, and
generate a SQL statement in code behind the form for the
query? The product list box would allow multiple
selections. The other list box on the form, credit
quality, would not.

My existing "canned" queries us a combination of tables
and queries linked in one query to populate the result
form. Now I'm trying to build an "ad hoc" form.

Thanks for your suggestions.

Kitty

 

[Roger Carlson]

On my website, see sig below, are several small sample databases named:
CreateQueries2.mdb, CreateQueries3.mdb, CreateQueries4.mdb,
CreateQueries5.mdb that are all concerned with doing this. They get
progressively complex, so you might want to start with 2 and work up.

 

[Marshall Barton]

I need to have a form where the user will do a combination
of selecting items from lists and inputting other items.
I then need to take what the user has selected/input and
use it as query parameters.

For example, the user would be able to select multiple
product types from a list box, input a dollar amount,
input a couple of different types of dates and pick
another classification code from a list box. To
illustrate, the user could want a list of all accounts
using dairy or grain products with total sales over $XX
since YY date with a credit class of A. Or they may want
just all dairy and grain customers regardless of size,
date or credit quality.

Help! Would it be best to dump the results of what the
user selects into a table and use that as parameters in
the query? Should I capture the information some how, and
generate a SQL statement in code behind the form for the
query? The product list box would allow multiple
selections. The other list box on the form, credit
quality, would not.

My existing "canned" queries us a combination of tables
and queries linked in one query to populate the result
form. Now I'm trying to build an "ad hoc" form.

The best (probably the only) way to do that complex kind of
searching is to construct the SQL for a query. One very
powerful capability that can be a big help (and a headache
too) is the BuildCriteria function (the same code that the
query design grid uses for criteria). Check it out in Help
and play around with it to get a feel for how to use it
effectively.

 

[Kitty]

Thank you, Marshall and Roger, for your quick replies.
I'll try both your suggestions.

Kitty

 

[mfg2529]

Kitty,

I can relate to your problem as I am trying to do something similar myself! I've been racking my
brain on this one and once I get some sense of what to do, I will pass it on to you as well!

I am an advance MS Acess person but have never used SQL. I have written lots of programs but
this is the most complex and I am stumped too.

Good luck and I will let you know as well if I figure anything out too!
Dao Weeman
Microboard Processing, Inc
203-881-4338