Report with Stored Procedure

[Everaldo Ricardo]

what is the best way to make a report with a Stored Procedure as
RecordSource ?

 

[Everaldo Ricardo]

I forgot to say: The Parameters are in a Form

 

[ambroski]

1. Create a form that accepts input parameters.
2. Create an SP that accepts these parameters from the form created in
Step 1.
3. Create your report and set the data source/control source = the
query created in Step 2.

 

[ambroski]

1. Create a form that accepts input parameters.
2. Create an SP that accepts these parameters from the form created in
Step 1.
3. Create your report and set the data source/control source = the
query created in Step 2.

 

[Everaldo Ricardo]

1 - how can I create Form that accepts input parameters ?
2 - a stored procedure that accepts parameters from the from is different of
any other stored procedure ?

 

[ambroski]

1. Create spRicardo e.g.

create procedure spRicardo
(
@ID int
)
as
select * from tblRicardo where id = @ID


2. Create a report and set its record source to spRicardo
3. Create a form with at least one control to store parameter say,
textbox. Create a button (if needed, a button is not the only option to
achieve this) to fire the 'open report'.
4. Behind the code of that button :
i)execute spRicardo (sending parameter value(s) to it).
ii)Open that Report

 

[J. Clay]

If you are using a Stored Procedure w/Parameters for a record source for a
form or report you can define the parameters in the InputParameters property
of the Form/Report. It is the last line of the data tab.

In here you can reference another forms controls. i.e. If I have Report1
that is based off of stored procedure "SP_Alpha" that requires a parameter
called @Key. The parameter needs to be defined as the KeyFieldValue on
FormA, which is in the text control "txtKey".

Assuming the parameter is and Integer, Report1's Data Source would be
"dbo.SP_Alpha"

In Report1's Input Parameter you would put something like @Key INT =
Forms("FormA")("txtKey")

This gives you dynamic control of a form or report based off of a
parameterized Stored porcedure.

HTH,
Jim