Passing Parameters to a pass thru query

  • Thread starter Thread starter JOE
  • Start date Start date
J

JOE

Hi All,
I have set up a pass through query that runs a stored
procedure in my SQL DB. I created a report from that
query as well. I would like to pass three prameters from
a form that would run the stored Procedure and open the
report.

I tried to modify the pass thru query to say
"exec SPname [forms]![replrptsys]![cmdbranch],[forms]!
[replrptsys]![begDate],[forms]![replrptsys]![EndDate]"

This errors out on the "!"

Any help is greatly appreciated.

Thanks,
Joe
 
I use a generic function to change the SQL of the p-t.


Function ChangeSQL(pstrQuery as String, _
pstrSQL as String) as boolean
Currentdb.QueryDefs(pstrQuery).SQL = pstrSQL
ChangeSQL = (Currentdb.QueryDefs(pstrQuery).SQL = pstrSQL)
End Function
 
Thanks Duane,
But I do not understand this at all.
I understand where I would put this code, but where do I
specify the parameters coming from the form's input boxes?

To run my Stored Procedure in SQL I would use the
following in query analyzer
" exec ComProdVsBudget_sp branch_name,startdate,enddate"

I want the users to input on a form the three parameters
and have the report or pass thru query pass the 3 values
to my SP.

Thanks again,
Joe
 
Assuming a form with three text boxes and a button to run a report based on
a P-T query. The On Click event of the button would have code like:

Dim strSQL as String
strSQL = "exec ComProdVsBudget_sp '" & _
Me.txtBranchName & "', '" & Me.txtStartDate & _
"', '" & Me.txtEndDate & "'"
If Not ChangeSQL("qsptMyPT", strSQL) Then
'
End If
 
I don't know Dyane,
I can't seem to figure out how to get either of the two
samples you sent to work.

I still dont see how I pass the 3 paramteters to my SQL SP.
I cant pass this new statement to the ChangeSQL function
either.


Thanks again for your help.
Joe
 
You need to save the ChangeSQL() function in a standard module. You can then
call it from code. My code in the most recent posting, assumed you had a
form with three text boxes (each containing one of your parameters). Then
you can place a button or use any other event to call the ChangeSQL()
function to update the SQL property of the Pass-Through query.

If you can't follow this, please advise us regarding what you have so far
and where you are confused.
 
Back
Top