Help with Complex Query By Form

Hello Everyone

Access 2000

Looking for some suggestions and advice on how to proceed with this. I hope
its not as complicated as it seems to me right now.

I've got an unbound main form with a subform bound to a query
[qrySearchResults]. What I'd like to do is have the user choose criteria
from four objects on the main form then click a button that displays the
filtered results on the subform.

What I have so far on the main form going from left to right is this:

[lstboxdept] an unbound list box of departments. when the user selects a
department, the next list box [lstmajor] displays the majors associated with
those departments. This works fine.

then an unbound option [opthonors] group with a yes and no button

then another unbound list box of states [lststate].

What makes this more complicated is the fact that the user may only want to
filter on state, or honor and state, or just department and major, etc.

How do I pass the criteria from the four unbound objects to the subform's
query? Should I put the code in the onclick event of the button on the main
form or does the code go in the query grid?

Thank you for any and all help with this. I feel like I'm in over my head
on this one!

William

re: Help with Complex Query By Form

You might try using Filter by Form instead of using a query. This is like
Query by Form without the query.

Con Giacomini

"William Wisnieski" <william.wisnieski@verizon.net> wrote in message
news:DyJvb.4159$yy5.931@nwrdny01.gnilink.net...[color=blue]
> Hello Everyone
>
> Access 2000
>
> Looking for some suggestions and advice on how to proceed with this. I[/color]
hope[color=blue]
> its not as complicated as it seems to me right now.
>
> I've got an unbound main form with a subform bound to a query
> [qrySearchResults]. What I'd like to do is have the user choose criteria
> from four objects on the main form then click a button that displays the
> filtered results on the subform.
>
> What I have so far on the main form going from left to right is this:
>
> [lstboxdept] an unbound list box of departments. when the user selects a
> department, the next list box [lstmajor] displays the majors associated[/color]
with[color=blue]
> those departments. This works fine.
>
> then an unbound option [opthonors] group with a yes and no button
>
> then another unbound list box of states [lststate].
>
> What makes this more complicated is the fact that the user may only want[/color]
to[color=blue]
> filter on state, or honor and state, or just department and major, etc.
>
> How do I pass the criteria from the four unbound objects to the subform's
> query? Should I put the code in the onclick event of the button on the[/color]
main[color=blue]
> form or does the code go in the query grid?
>
> Thank you for any and all help with this. I feel like I'm in over my[/color]
head[color=blue]
> on this one!
>
> William
>
>[/color]

re: Help with Complex Query By Form

Determine which controls have items selected or whatever. In the case
of a listbox, check the ItemsSelected.Count property. Then build your
query on the fly - just stuff it into a string variable. Then assign
the recordsource for the subform to that sql statement.