Alternative to using =[Forms]![Form]![Control] as a criteria to filter a query

[Yair Sageev]

I have heard somewhere that MSDE and SQL Server don't like it if your
queries reference form controls as criteria to link a result with a specific
record in the underlying table of that form. Is there another way to do
this that will make my forms compatible in the future, when/if we upsize?

In other words, it would be great if the query could accept the parameter
from the form, report, or eslewhere, instead of interrogating the form for a
control value.

Thanks.

 

[Tom Ellison]

Dear Yair:

Hello again!

As a developer who works in ADPs all the time, we simply use dynamic
queries nearly all the time. We build the SQL string in code, putting
the value of controls into the string rather than a reference to the
control itself. On any kind of pass-through query, this is the only
way it can be done, meaning that client/server architecture does not
give the server access to any client information other than what the
client chooses to provide.

This approach will work fine with Jet, so you would have the
flexibility of creating forms that are more compatible at least in
this issue.

Other than using Jet, where the engine is running on the client, this
is the rule everywhere else in the industry. (Not that you won't find
some other exception, but that's what rules are for!)

I have heard somewhere that MSDE and SQL Server don't like it if your
queries reference form controls as criteria to link a result with a specific
record in the underlying table of that form. Is there another way to do
this that will make my forms compatible in the future, when/if we upsize?

In other words, it would be great if the query could accept the parameter
from the form, report, or eslewhere, instead of interrogating the form for a
control value.

Thanks.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Yair Sageev]

Thanks guys. Your advice is kinda depressing! I love the query builder!

I have a feeling though that this must be easier. What I have in mind is
basically a parameter query, except that the user doesn't enter the
parameter, that parameter is passed by the form or whatever control is
performing a DLookup.

In every other type of computer programming you have a function which
excepts parameters. Surely there is a way to pass an argument/value to a
query without user intervention.

 

[Yair Sageev]

Make that "accepts" parameters.

Anyway, I'm wondering if "criteria" below will fill the parameter expected
by a parameter query.

DLookup(expr, domain, [criteria])

The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to
return. It can be a string expression identifying a field in a table or
query, or it can be an expression that performs a calculation on data in
that field. In expr, you can include the name of a field in a table, a
control on a form, a constant, or a function. If expr includes a function,
it can be either built-in or user-defined, but not another domain aggregate
or SQL aggregate function.
domain A string expression identifying the set of records that
constitutes the domain. It can be a table name or a query name for a query
that does not require a parameter.
criteria An optional string expression used to restrict the range of
data on which the DLookup function is performed. For example, criteria is
often equivalent to the WHERE clause in an SQL expression, without the word
WHERE. If criteria is omitted, the DLookup function evaluates expr against
the entire domain. Any field that is included in criteria must also be a
field in domain; otherwise, the DLookup function returns a Null.

 

[Tom Ellison]

Dear Yair:

But, you can still use Query Builder. Build a sample of the query
with a fixed value for the parameter, and copy the SQL to your code.
Then replace the fixed value with code that concatenates the desired
value from a control. See, you get the best of both worlds!

Thanks guys. Your advice is kinda depressing! I love the query builder!

I have a feeling though that this must be easier. What I have in mind is
basically a parameter query, except that the user doesn't enter the
parameter, that parameter is passed by the form or whatever control is
performing a DLookup.

In every other type of computer programming you have a function which
excepts parameters. Surely there is a way to pass an argument/value to a
query without user intervention.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Yair:

Your [criteria] would have to be typed by the user unerringly. So
much better to have a combo box of the acceptable values and just
enter it on a form. Fewer problems that way, guaranteed.

Make that "accepts" parameters.

Anyway, I'm wondering if "criteria" below will fill the parameter expected
by a parameter query.

DLookup(expr, domain, [criteria])

The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to
return. It can be a string expression identifying a field in a table or
query, or it can be an expression that performs a calculation on data in
that field. In expr, you can include the name of a field in a table, a
control on a form, a constant, or a function. If expr includes a function,
it can be either built-in or user-defined, but not another domain aggregate
or SQL aggregate function.
domain A string expression identifying the set of records that
constitutes the domain. It can be a table name or a query name for a query
that does not require a parameter.
criteria An optional string expression used to restrict the range of
data on which the DLookup function is performed. For example, criteria is
often equivalent to the WHERE clause in an SQL expression, without the word
WHERE. If criteria is omitted, the DLookup function evaluates expr against
the entire domain. Any field that is included in criteria must also be a
field in domain; otherwise, the DLookup function returns a Null.

Thanks guys. Your advice is kinda depressing! I love the query builder!

I have a feeling though that this must be easier. What I have in mind is
basically a parameter query, except that the user doesn't enter the
parameter, that parameter is passed by the form or whatever control is
performing a DLookup.

In every other type of computer programming you have a function which
excepts parameters. Surely there is a way to pass an argument/value to a
query without user intervention.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts