Can a SQL Pass Through query use a parameter from a Form Control?

W

woody

I have a bit of a newbie question (basically stated in the subject of
the posting). If need to get some data out of a SQL Server Database.
Can I use a control from a form to supply a parameter. I've created a
stored procedure with the parameter I need, I just dont know how to
send the data from the form to the SPT query.

So I need to get data out of [Forms]![Form1]![Combo6] to my SPT query.
Can I do it?

Any help is greatly appreciated.

Thanks!
 
D

Duane Hookom

You can use a little DAO code like:

Dim strSQL as String
strSQL = "EXEC spYourSP '" & Forms!Form1]![Combo6] & "'"
CurrentDb.QueryDefs("qsptMySPT").SQL = strSQL
 
W

woody

Thanks for the tip.. but where do I execute that code from within
Access?

Thanks, again!

Duane said:
You can use a little DAO code like:

Dim strSQL as String
strSQL = "EXEC spYourSP '" & Forms!Form1]![Combo6] & "'"
CurrentDb.QueryDefs("qsptMySPT").SQL = strSQL


--
Duane Hookom
MS Access MVP

woody said:
I have a bit of a newbie question (basically stated in the subject of
the posting). If need to get some data out of a SQL Server Database.
Can I use a control from a form to supply a parameter. I've created a
stored procedure with the parameter I need, I just dont know how to
send the data from the form to the SPT query.

So I need to get data out of [Forms]![Form1]![Combo6] to my SPT query.
Can I do it?

Any help is greatly appreciated.

Thanks!
Click to expand...
Click to expand...
 
W

woody

When I try to run that from withing the SPT query, I get an ODBC
error... "Invalid syntax near the keyword 'as'. So not sure if I'm not
doing something properly.

Thanks again for the assistance!
Thanks for the tip.. but where do I execute that code from within
Access?

Thanks, again!

Duane said:
You can use a little DAO code like:

Dim strSQL as String
strSQL = "EXEC spYourSP '" & Forms!Form1]![Combo6] & "'"
CurrentDb.QueryDefs("qsptMySPT").SQL = strSQL


--
Duane Hookom
MS Access MVP

woody said:
I have a bit of a newbie question (basically stated in the subject of
the posting). If need to get some data out of a SQL Server Database.
Can I use a control from a form to supply a parameter. I've created a
stored procedure with the parameter I need, I just dont know how to
send the data from the form to the SPT query.

So I need to get data out of [Forms]![Form1]![Combo6] to my SPT query.
Can I do it?

Any help is greatly appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

The code that I suggested could be run in a form from a command button or
where ever you wanted. If you got an error message then you need to reply
back with your code and the exact error message.


--
Duane Hookom
MS Access MVP

woody said:
When I try to run that from withing the SPT query, I get an ODBC
error... "Invalid syntax near the keyword 'as'. So not sure if I'm not
doing something properly.

Thanks again for the assistance!
Thanks for the tip.. but where do I execute that code from within
Access?

Thanks, again!

Duane said:
You can use a little DAO code like:

Dim strSQL as String
strSQL = "EXEC spYourSP '" & Forms!Form1]![Combo6] & "'"
CurrentDb.QueryDefs("qsptMySPT").SQL = strSQL


--
Duane Hookom
MS Access MVP

I have a bit of a newbie question (basically stated in the subject of
the posting). If need to get some data out of a SQL Server Database.
Can I use a control from a form to supply a parameter. I've created
a
stored procedure with the parameter I need, I just dont know how to
send the data from the form to the SPT query.

So I need to get data out of [Forms]![Form1]![Combo6] to my SPT
query.
Can I do it?

Any help is greatly appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
W

woody

I've got it to work. I was just being stupid and using it in the wrong
place.

Thanks so much for your help!

Duane said:
The code that I suggested could be run in a form from a command button or
where ever you wanted. If you got an error message then you need to reply
back with your code and the exact error message.


--
Duane Hookom
MS Access MVP

woody said:
When I try to run that from withing the SPT query, I get an ODBC
error... "Invalid syntax near the keyword 'as'. So not sure if I'm not
doing something properly.

Thanks again for the assistance!
Thanks for the tip.. but where do I execute that code from within
Access?

Thanks, again!

Duane Hookom wrote:
You can use a little DAO code like:

Dim strSQL as String
strSQL = "EXEC spYourSP '" & Forms!Form1]![Combo6] & "'"
CurrentDb.QueryDefs("qsptMySPT").SQL = strSQL


--
Duane Hookom
MS Access MVP

I have a bit of a newbie question (basically stated in the subject of
the posting). If need to get some data out of a SQL Server Database.
Can I use a control from a form to supply a parameter. I've created
a
stored procedure with the parameter I need, I just dont know how to
send the data from the form to the SPT query.

So I need to get data out of [Forms]![Form1]![Combo6] to my SPT
query.
Can I do it?

Any help is greatly appreciated.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Top