Calling SQL Stored Procedure with Parameter

TyFer

New member
Local time
Today, 05:25
Joined
Dec 6, 2015
Messages
9
Hi,

I’m in need of help again. On my form there is a button and when clicked opens another form. I need to populate this form with results from a SQL stored procedure. The stored procedure has one parameter, the parameter is the value from the ProductID field on the main form.

This is the code I have been trying to use, but I can’t get it to work. I don’t get any errors when I open the form, but it’s not recognizing the field names from my procedure so nothing is getting returned. When I run the procedure in SQL, there should definitely be data. Any help, would be greatly appreciated.

Code:
Dim qdf As DAO.QueryDef
Dim str As String
Dim ProdID As String

ProdID = Forms![FormName]!ProductID
 
str = "EXEC proc_GetLYSHipmentHistory '" & ProdID & "'"
Set DB = CurrentDb()
set qdf = DB.CreateQueryDef("")

qdf.Connect = "ODBC;Driver=SQL Server;Server=servername;trusted_connection=Yes;Database=databasename"
qdf.SQL = str
qdf.ReturnsRecords = True
 
Well, I would have a saved pass through query and change the SQL of it. That query would be the record source of the form.
 
Thanks pbaldy, that's exactly what I did and things work now..thanks so much!
 

Users who are viewing this thread

Back
Top Bottom