Export recordset from ADP project

[Angy007]

In a ADP project, is there a way to export a recordset of a ListBox which
displays data from a stored procedure with parameter?
I have tried DoCmd.OutputTo acOutputStoredProcedure ... method, but ask me to
input parameter again.
Many thanks
Angy

 

[Sylvain Lafontaine]

Not sure to understand the question about the ListBox. Try to add the EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.

 

[Angy007 via AccessMonster.com]

Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a ListBox
that
displays the results of a stored procedure, a command button which run the
stored
procedure.

Here my code:

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SP_name"
cmd.CommandType = adCmdStoredProc

Set Me.ListBox.Recordset = cmd.Execute(, Me.TextBox)

...

Now, I need to export what the ListBox shows after the user's research is
finished.

I'm trying the suggested solutions, but I don't know how specify the
control's name
to passing parameter .

...
DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP ? "


Thanks
Angelo

Not sure to understand the question about the ListBox. Try to add the EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.

In a ADP project, is there a way to export a recordset of a ListBox which
displays data from a stored procedure with parameter?

[quoted text clipped - 3 lines]

Many thanks
Angy

 

[Sylvain Lafontaine]

The DoCmd.OutputTo is run as a macro and as such, you cannot use in-place
parameter like ?

You must dynamically built the whole string:

Dim sql as string
sql = "EXEC MySP " & FirstParameter
DoCmd.OutputTo acOutputStoredProcedure, sql, ....

If the parameter is a string, you must enclose it between single quote (and
doubling any embedded single quote to two single quotes):

sql = "EXEC MySP '" & Me.TextBox & "'"

or:
sql = "EXEC MySP '" & Replace (Me.TextBox, "'", "''") & "'"

For your ListBox, you can do the same thing and use a sql string for the
RowSource instead of setting the Recordset:

Me.ListBox.RowSource = sql

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a
ListBox
that
displays the results of a stored procedure, a command button which run the
stored
procedure.

Here my code:

Dim cmd as ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SP_name"
cmd.CommandType = adCmdStoredProc

Set Me.ListBox.Recordset = cmd.Execute(, Me.TextBox)

..

Now, I need to export what the ListBox shows after the user's research is
finished.

I'm trying the suggested solutions, but I don't know how specify the
control's name
to passing parameter .

..
DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP ? "


Thanks
Angelo

Not sure to understand the question about the ListBox. Try to add the
EXEC
command before the name of the SP and the parameters after:

DoCmd.OutputTo acOutputStoredProcedure, "EXEC MySP FirstParameter,
SecondParameter, ...."

For string and date parameters, enclose them with single quotes. If this
doesn't work, try replacing acOutputStoredProcedure with
acOutputSQLStatement. If nothing work, post back here with an example of
your code so that we can understand what you mean to do with this ListBox.

In a ADP project, is there a way to export a recordset of a ListBox
which
displays data from a stored procedure with parameter?

[quoted text clipped - 3 lines]

Many thanks
Angy

 

[Angy007 via AccessMonster.com]

Many many thanks for your reply.
It's exactly what I wanted to do.

Angy

The DoCmd.OutputTo is run as a macro and as such, you cannot use in-place
parameter like ?

You must dynamically built the whole string:

Dim sql as string
sql = "EXEC MySP " & FirstParameter
DoCmd.OutputTo acOutputStoredProcedure, sql, ....

If the parameter is a string, you must enclose it between single quote (and
doubling any embedded single quote to two single quotes):

sql = "EXEC MySP '" & Me.TextBox & "'"

or:
sql = "EXEC MySP '" & Replace (Me.TextBox, "'", "''") & "'"

For your ListBox, you can do the same thing and use a sql string for the
RowSource instead of setting the Recordset:

Me.ListBox.RowSource = sql

Sorry, what I mean is this:
in a form I have 3 controls, a textBox where user input parameter, a

[quoted text clipped - 48 lines]