ADP Report Parameters to a Stored Procedure

[Mark Flippin]

This is for a reporting problem using:
Access 2000
SQL Server 2000

Both at SP3

I've a stored procedure in SQL Server 2000 which builds a result set
from a disparate set of tables, utilizing a temp table. The procedure
takes two parameters to specify the criteria on selecting the table
information for inclusion in the result set, builds the temp table,
and the returns the rows.

I intend on using the result set in a report in an ADP, and eventually
and ADE.

I want to be able to, at execution time, have the report pass the two
parameters to the stored procedure during "OnOpen" and then process
the result set.

When designing the report, I get the message:

Microsoft Access can't find the object 'Microsoft Access can't find
the object 'procedure name''

According to the information I've been able to find, because the
procedure must be executed to develop the result set, this is normal
processing and one should simply type in the column names.

And indeed, the report works perfectly if I place the stored procedure
name and call parameters in the report properties (RecordSource and
InputParameters) and then run the report.

However, I've been unable to pass the parameters at run time.

Per a Microsoft article, InputParameters does not work at runtime and,
by golly, they don't.

But the articles and news group postings imply that appending the call
parameters to the RecordSource property (either named or positional)
will do the job.

But all I get is "Microsoft Access can't find the object 'Microsoft
Access can't find the object 'procedure name parm1 parm2'' or prompts
for the two parameters.

Regardless of the format. Even if the format that works fine when
hardcoded in the report's properties RecordSource and InputParameters.

I gotta believe there's a way to do this.

Does anyone have a working piece of code where they pass parameters to
a stored procedure from a report at runtime in an adp?

[Lyle Fairfield]

Mark Flippin <me******@comcast.net> wrote in
news:q5********************************@4ax.com:

Per a Microsoft article, InputParameters does not work at runtime and,
by golly, they don't.

When would they work?

Try

Set the inputparameters property to = SomePublicFunctionInAStandardModule()

Program the function to return a string of the parameters you want to use as:

@ParameterName int=32, @OtherParamterName varchar(50)='Bizarro'

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Tom van Stiphout]

On 14 Sep 2004 02:36:33 GMT, Lyle Fairfield <Lo******@FFDBA.Com>
wrote:

Bizarre: the spelling error is at the 34th character.
-Tom.

@ParameterName int=32, @OtherParamterName varchar(50)='Bizarro'

[vtashore]

Why not write the values of the input parameters into unbound text fields on
a hidden form at run time and have your report's input parameter refer to
the controls' properties? Alternately, launch the report from this form to
ensure that it is open.

@Parm1 = Forms!frmHidden!Parm1, @Parm2 = Forms!frmHidden!Parm2

Alternately, launch the report from this form to ensure that it is open.

Hope this helps.
Randy Shore
"Mark Flippin" <me******@comcast.net> wrote in message
news:q5********************************@4ax.com...

This is for a reporting problem using:
Access 2000
SQL Server 2000

Both at SP3

I've a stored procedure in SQL Server 2000 which builds a result set
from a disparate set of tables, utilizing a temp table. The procedure
takes two parameters to specify the criteria on selecting the table
information for inclusion in the result set, builds the temp table,
and the returns the rows.

I intend on using the result set in a report in an ADP, and eventually
and ADE.

I want to be able to, at execution time, have the report pass the two
parameters to the stored procedure during "OnOpen" and then process
the result set.

When designing the report, I get the message:

Microsoft Access can't find the object 'Microsoft Access can't find
the object 'procedure name''

According to the information I've been able to find, because the
procedure must be executed to develop the result set, this is normal
processing and one should simply type in the column names.

And indeed, the report works perfectly if I place the stored procedure
name and call parameters in the report properties (RecordSource and
InputParameters) and then run the report.

However, I've been unable to pass the parameters at run time.

Per a Microsoft article, InputParameters does not work at runtime and,
by golly, they don't.

But the articles and news group postings imply that appending the call
parameters to the RecordSource property (either named or positional)
will do the job.

But all I get is "Microsoft Access can't find the object 'Microsoft
Access can't find the object 'procedure name parm1 parm2'' or prompts
for the two parameters.

Regardless of the format. Even if the format that works fine when
hardcoded in the report's properties RecordSource and InputParameters.

I gotta believe there's a way to do this.

Does anyone have a working piece of code where they pass parameters to
a stored procedure from a report at runtime in an adp?

[Mark Flippin]

Nope, that don't work. Access just complains about the number of
parameters.

The only method I've been able to get to work is to have the
parameters as text boxes on an existing, open form.

In which case, you hardcode in the report's "InputParameters"

=forms!form1.tbxReportParm1,forms!form1.tbxReportP arm2

Oh, by the way, you have to hardcode the procedure name in the
report's "RecordSource" property or that don't work either. If you
provide the procedure name in the "onOpen" event, you get the
parameter prompts.

Interestingly enough, you can modify the contents of these text boxes
in the report's "onOpen" event and have it take. Which is kinda weird,
trying to comprehend the timing of the events. Why can I change a text
box in the parm string, yet not supply the parm directly?

This will work, but it seems ugly

Mark

On 14 Sep 2004 02:36:33 GMT, Lyle Fairfield <Lo******@FFDBA.Com>
wrote:

Try

Set the inputparameters property to = SomePublicFunctionInAStandardModule()

Program the function to return a string of the parameters you want to use as:

@ParameterName int=32, @OtherParamterName varchar(50)='Bizarro'

[Lyle Fairfield]

Mark Flippin <me******@comcast.net> wrote in
news:t2********************************@4ax.com:

Nope, that don't work. Access just complains about the number of
parameters.

@ParameterName int=32, @OtherParamterName varchar(50)='Bizarro'

Actually it does work when it is implemented correctly.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Mark Flippin]

OK, I'm game.

I call bull****

Please provide the correct implementation, including the stored
procedure definition and project references.
On 15 Sep 2004 02:36:47 GMT, Lyle Fairfield <Lo******@FFDBA.Com>
wrote:

Actually it does work when it is implemented correctly.
Lyle

[Mark Flippin]

Randy,

That's exactly what I've decided to do (option 2, launch the report
frm the form to ensrue it's open). This works fine for the current
project.

Mark

On Tue, 14 Sep 2004 09:23:57 -0700,
"vtashore@Ta*********@adelphia.net" <vt******@adelphia.net> wrote:

Why not write the values of the input parameters into unbound text fields on
a hidden form at run time and have your report's input parameter refer to
the controls' properties? Alternately, launch the report from this form to
ensure that it is open.

@Parm1 = Forms!frmHidden!Parm1, @Parm2 = Forms!frmHidden!Parm2

Alternately, launch the report from this form to ensure that it is open.

Hope this helps.
Randy Shore

[Leendert van Staalduinen]

I found a lot of misunderstanding in this thread in the sense of "this
works" or "this does not work". I work with Access ADP 2000 plus SQL
Server. Let me give some hints:
- I could use statements like: rptActive.RecordSource =
"dbo.procMyReport".
Just do not state it in the Report_Open, but in the Report_Activate
event.
- I can stretch this idea a little bit further (but won't be too
specific):
"Always try the Report_Activate event (and NOT the Report_Open) for
data processing".

- It is correct to have input parameter specifications like:
"@Parm1 = Forms!frmHidden!Parm1, @Parm2 = Forms!frmHidden!Parm2".
Still, I think this is an awkward kind of programming. I made a Public
property and had a parameter specification like: "@Parm1 =
Forms!frmMyForm!MyProperty". What's the advantage:
- I do not need an additional form to reach my goal.
- Neither do I need an additional control somewhere. Make it a
property, that's a more parsimoneous (lightweight) approach.
- The above parameter specification works for a public property on an
open form, it dis not work for a public property in a standard module
(sorry, did not test yet for a property in a class).

Let me provide some more information. The most informative source for
this kind of questions is the widely-acclaimed Chipman and Byron
(2001). "Microsoft Access Developer's Guide to SQL Server". In their
chapter 12 on reports, they describe a few techniques that don't work
but should. I did not check every detail, but I found a few of their
non-working techniques working for me (in ADP as well as in ADE). My
guess for now: "It's the Report_Activate event that makes the
difference".

Leendert van Staalduinen,
van Staalduinen Data Management bv.
Mark Flippin <me******@comcast.net> wrote in message news:<71********************************@4ax.com>. ..

Randy,

That's exactly what I've decided to do (option 2, launch the report
frm the form to ensrue it's open). This works fine for the current
project.

Mark

On Tue, 14 Sep 2004 09:23:57 -0700,
"vtashore@Ta*********@adelphia.net" <vt******@adelphia.net> wrote:

Why not write the values of the input parameters into unbound text fields on
a hidden form at run time and have your report's input parameter refer to
the controls' properties? Alternately, launch the report from this form to
ensure that it is open.

@Parm1 = Forms!frmHidden!Parm1, @Parm2 = Forms!frmHidden!Parm2

Alternately, launch the report from this form to ensure that it is open.

Hope this helps.
Randy Shore