[AccessD] Reasons to Use Parameter Functions; WAS: Crosstab uses form criteria not

Kenneth Ismert kismert at gmail.com
Tue Jun 22 18:28:50 CDT 2010


> Heenan, Lambert:
> Right. It's kinda weird, but for crosstabs you have to tell it about the
> parameters on the query menu, but still use the criteria row like a normal
> query.
>
> Bob Heygood
> ... When I try to use the same query as a source for a second query
> (crosstab), Access barfs and says:
> "The Microsoft jet database engine does not recognize
> '{Forms]![frmReports]![cboMonths]' as a valid field name or expression"
>
> Does not the same jet engine work the same when creating a crosstab?
>

You can use a global parameter function in a crosstab query without messing
with the parameters dialog. It works just like it does in normal queries.

Once again, the common thread I've been observing recently is control
references in queries, forms and reports causing problems and wasting
development time.

So, not to harp (OK, to nicely harp), I'm going to rundown my list of
reasons why using global parameter functions to coordinate queries, forms
and reports is better than control references:

Problems Avoided:
==============
1. Functions work when the source form is closed

2. Functions are easier to debug, compared with David's OpenArgs issue

3. As mentioned, functions work with no special tweaking in crosstab
queries, or subqueries underneath

4. Using functions in subform queries avoids certain syncing issues between
parent and subform control references. This is useful when you need more
flexibility than what standard Child and Master field linking gives you, and
it performs just as well.

Enhancements over Form References:
============================
1. Functions allow any number of forms to open a common report, and sync it
to the current form

2. Functions can be flexibly implemented -- use either an 'all-in-one'
function based on a collection, or a group of independent functions for
stricter naming practice and individual defaults.

3. Functions put all of your parameters in one place -- you don't have to
search every form, query and report to find out what control references
you're using

4. Forms based on queries restricted with parameter functions outperform
Filters

5. Search forms using 'optional' function parameters allow the user to
search via an interface, while still letting them use the Filter for odd
situations. This gives the user flexibility, and saves you from coding
one-off search requests.

6. Functions provide continuity by remembering the state of the application
session, regardless of what forms the user closes or opens.

Wrap Up
=======
In my experience, forms work best when you treat their controls as private
members. That means forms should only reference their own controls. No other
object should reference an outsider's control. Passing parameters via
function accomplishes this. This decouples the objects in your database,
making your applications more predictable, flexible and robust.

If you're using a 'parameter form', (a hidden form whose controls hold
global parameters), it should be a no-brainer to move to parameter
functions.

A lot of these benefits are provided by the newer 'parameters collection'
(whose proper name escapes me), but functions are available in all versions.

-Ken



More information about the AccessD mailing list