Using Stored Procedure with Parms to Feed Report

[jmeadows7]

I want to begin making better use of my server to feed reports. I would like to feed a report utilizing a stored procedure. I know how to do this with a very simple example - i.e. Select Cust_nbr, cust_name from customers ....

However, I would like the user to be able to select parameters on a form, select the report to run, and then click Preview. I'd like to be able to invoke the Stored procedure with the parameters that the user input and feed the results directly to my report. I do know how to invoke a stored procedure and put the data into an interim reporting table that I then use to feed the report. However, I'd like to go directly from stored procedure to the report. Should I use a record set to store the results from the stored procedure and then feed the recordset to the report?

Thanks for any suggestions.

 

[PWise]

this is a system that i use

in report open

set me.recordset = ExecuteAdoRS("SPname",4,0,froms!formname!Pram1controlname,froms!formname!Pram2controlname,froms!formname!Pram3controlname.....

supporting code

Option Compare Database
Option Explicit
Public Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command


Function InitializeAdo()
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open CurrentProject.Connection
End If
End Function

Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset

'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
     cmd.Parameters(Prams) = AdoPrams(Prams)
    'Debug.Print cmd.Parameters.Item(Prams).Name, cmd.Parameters.Item(Prams).Value
Next Prams
Set ExecuteAdoRS = cmd.Execute(a)
If adoCommandType = 4 Then AdoPrams(0) = cmd(0)
End Function

 

[jmeadows7]

Thanks for the suggestion. I'll try it out.

Thanks,