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

[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!

 

[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

 

[woody]

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

Thanks, again!

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!

 

[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!

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!

 

[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

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!

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!

 

[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!

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

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!