Using form criteria in ADP view?

[M Wells]

Hi All,

Is it possible to use form criteria in a query / view in an Access
2003 ADP with SQL Server as the backend?

ie something like:

select * from mytable where recid = forms![frm_myform]![recid]

I'm trying to do something like this as the basis for a subform but I
get a syntax error because of the existence of the "!" characters.

Any help appreciated!

Much warmth,

Murray

[Steve Jorgensen]

No, but there is a similar trick you can do with ADPs. If you create a stored
procedure to use as the Row Source of a combo or list box, and the stored
procedure takes a parameter with the same name (except for the additional
required @ sign) as a control on the same form, Access will automatically pass
the control's value to the stored procedure's parameter.

The same trick works with subforms, too. If the subform's Record Source is a
stored procedure, and the form has a control with the same name as a paramter
of the stored procedure, it will be automatically passed.

On Wed, 05 May 2004 14:44:15 GMT, M Wells <pl**********@planetthoughtful.org>
wrote:

Hi All,

Is it possible to use form criteria in a query / view in an Access
2003 ADP with SQL Server as the backend?

ie something like:

select * from mytable where recid = forms![frm_myform]![recid]

I'm trying to do something like this as the basis for a subform but I
get a syntax error because of the existence of the "!" characters.

Any help appreciated!

Much warmth,

Murray

[Albert D. Kallal]

You can't use a forms ref directly in the sql..but you can resolve it.

So, you can stuff the sql directly into the forms reocrdsouce.

dim strSql as string

strSql = "select * from mytable where recid = " & forms![frm_myform]![recid]

me.MySubForm.Form.RecordSouce = strSql
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[M Wells]

Ah!

Thanks Steve, another thing I was wondering about was how to pass
multiple criteria to a stored procedure from a form.

I think what you've written below solves that issue for me.

However, do stored procedures allow criteria to be passed when they
involve subqueries? I've noticed that functions don't seem to allow
you to pass criteria if the function involves a subquery. Seems like
an odd ommision, since just about every other subform I create is
doing something like, show all available records that aren't currently
included in another subform's recordset. With some playing around, I
can usually write the sql so that it doesn't use a subquery, but it's
a lot easier to simply use a 'where column not in (select column from'
statement.

Thanks for this tip!

Much warmth,

Murray
On Wed, 05 May 2004 16:53:37 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

No, but there is a similar trick you can do with ADPs. If you create a stored
procedure to use as the Row Source of a combo or list box, and the stored
procedure takes a parameter with the same name (except for the additional
required @ sign) as a control on the same form, Access will automatically pass
the control's value to the stored procedure's parameter.

The same trick works with subforms, too. If the subform's Record Source is a
stored procedure, and the form has a control with the same name as a paramter
of the stored procedure, it will be automatically passed.

On Wed, 05 May 2004 14:44:15 GMT, M Wells <pl**********@planetthoughtful.org>
wrote:

Hi All,

Is it possible to use form criteria in a query / view in an Access
2003 ADP with SQL Server as the backend?

ie something like:

select * from mytable where recid = forms![frm_myform]![recid]

I'm trying to do something like this as the basis for a subform but I
get a syntax error because of the existence of the "!" characters.

Any help appreciated!

Much warmth,

Murray

[John Winterbottom]

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:b9********************************@4ax.com...

Ah!

Thanks Steve, another thing I was wondering about was how to pass
multiple criteria to a stored procedure from a form.

If the form is bound to the procedure use the forms' InputParameters
property setting:

I think what you've written below solves that issue for me.

However, do stored procedures allow criteria to be passed when they
involve subqueries?
AFAIK the structure of the procedure has no influence on whether or not it
will accept parameters.
I've noticed that functions don't seem to allow
you to pass criteria if the function involves a subquery.

I've never seen this. Do you have a script to repro this problem?
I don't even know whether you can bind a function to a form - I've heard
that you can but not sure why you would ever want to.

[M Wells]

On Wed, 12 May 2004 09:51:07 -0400, "John Winterbottom"
<as******@hotmail.com> wrote:

I've noticed that functions don't seem to allow
you to pass criteria if the function involves a subquery.

I've never seen this. Do you have a script to repro this problem?
I don't even know whether you can bind a function to a form - I've heard
that you can but not sure why you would ever want to.

Hi John,

Using a function to return a recordset to a form using form criteria
is described in the following KnowledgeBase article:

http://support.microsoft.com/default...b;en-us;278400

Given that this was the recommendation Microsoft made, this is what I
did.

However, attempting to include an @variable in a function with a
subquery returns the error message I described in my original post.

Much warmth,

Murray