Check Box on Form to hide query Columns

[Guest]

I would like to create a form that allows the user to select a number of
parameters that they could run a query (and eventually a report) on. They
would have the option to select Budget, Actuals, Staffing, Key Performance
Indicators, etc as possible choices.

Rather than creating a separate query for every possible iteration, I'd like
to know if there is a way that when the user click's a check box next to
Budget, the query only includes budget information, not any of the
aforementioned parameters. The same principle should be used for any number
of combinations.

Is this possible w/o using VBA Code? Should I be using something other than
check boxes to select the parameters? Since there is the possibility that
the user could select multiple parameters, combo/list boxes can't be used.
There is also upwards of abotu 25 different parameters that can be selected.

Thanks!

 

[Guest]

In that case you would have to add references in the query. This could look
something like [forms]![formname]![referencing field] and if you want the
option to show all when others are not selected then you have to place this
between a Like statement which could look like this:

Like([forms]![formname]![referencing field])&"*"

But 25 parameters on a query is very, very much. I think you should
reconsider the layout of your query and break it up into small portions of
parameters.

The last point I want to point out is the fact that if you use checkboxes
you'd get an additional 25 parameters which have to be checked first to see
if they are true or not. Many people use comboboxes instead.

Maurice

 

[Guest]

Is there a way that this can be done using VBA?

In that case you would have to add references in the query. This could look
something like [forms]![formname]![referencing field] and if you want the
option to show all when others are not selected then you have to place this
between a Like statement which could look like this:

Like([forms]![formname]![referencing field])&"*"

But 25 parameters on a query is very, very much. I think you should
reconsider the layout of your query and break it up into small portions of
parameters.

The last point I want to point out is the fact that if you use checkboxes
you'd get an additional 25 parameters which have to be checked first to see
if they are true or not. Many people use comboboxes instead.

Maurice

I would like to create a form that allows the user to select a number of
parameters that they could run a query (and eventually a report) on. They
would have the option to select Budget, Actuals, Staffing, Key Performance
Indicators, etc as possible choices.

Rather than creating a separate query for every possible iteration, I'd like
to know if there is a way that when the user click's a check box next to
Budget, the query only includes budget information, not any of the
aforementioned parameters. The same principle should be used for any number
of combinations.

Is this possible w/o using VBA Code? Should I be using something other than
check boxes to select the parameters? Since there is the possibility that
the user could select multiple parameters, combo/list boxes can't be used.
There is also upwards of abotu 25 different parameters that can be selected.

Thanks!

 

[John Spencer]

Customized report/query based on user input

You might want to consider the Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Is there a way that this can be done using VBA?

In that case you would have to add references in the query. This could
look
something like [forms]![formname]![referencing field] and if you want the
option to show all when others are not selected then you have to place
this
between a Like statement which could look like this:

Like([forms]![formname]![referencing field])&"*"

But 25 parameters on a query is very, very much. I think you should
reconsider the layout of your query and break it up into small portions
of
parameters.

The last point I want to point out is the fact that if you use checkboxes
you'd get an additional 25 parameters which have to be checked first to
see
if they are true or not. Many people use comboboxes instead.

Maurice

I would like to create a form that allows the user to select a number
of
parameters that they could run a query (and eventually a report) on.
They
would have the option to select Budget, Actuals, Staffing, Key
Performance
Indicators, etc as possible choices.

Rather than creating a separate query for every possible iteration, I'd
like
to know if there is a way that when the user click's a check box next
to
Budget, the query only includes budget information, not any of the
aforementioned parameters. The same principle should be used for any
number
of combinations.

Is this possible w/o using VBA Code? Should I be using something other
than
check boxes to select the parameters? Since there is the possibility
that
the user could select multiple parameters, combo/list boxes can't be
used.
There is also upwards of abotu 25 different parameters that can be
selected.

Thanks!