SQL Stored Procedure as Rowsource for listbox

[GLock]

I am trying to use a SQL Stored Procedure as the rowsource for my listbox
and am having trouble passing a parameter to the stored procedure. In the
rowsource property of the listbox I am using the following:

Exec WL_Nxt_stp_sfrm_sp @crit_id = Forms!frmWatchlist!lstWLCriteria

I get an error which reads "Invalid SQL Statement. Check the server filter
on the form record source."

As a test, I passed an integer to the stored procedure and it executed fine:

Exec WL_Nxt_stp_sfrm_sp @crit_id = 676

Can you tell me what I am doing wrong in attempting to pass a reference to a
listbox as my parameter?

Thank you!

 

[SFAxess]

Hi Gary,
In an Access ADP you cannot pass a reference to the form,
because the process is done on SQL Server. You can
however set the row source programmatically in a form's
class module in this fashion

Private Sub SetListSource()
Dim lngParam as long
Dim strProc as string

lngParam=Forms!frmWatchlist!lstWLCriteria
strProc="EXEC WL_Nxt_stp_sfrm_sp " & lngParam

me.lstYourListBox.RowSource=strProc
me.lstYourListBox.requery
End Sub

This should do what you need it to. You may want to put
this code in the form's On Current event, depending on
what you are doing.
Hope this helps. Let me know if it isn't what you were
looking for.
Thanks

 

[Vadim Rapp]

G> Exec WL_Nxt_stp_sfrm_sp @crit_id =
G> Forms!frmWatchlist!lstWLCriteria

change the name of the s.p. parameter from @crit_id to @lstWLCriteria; don't
specify any arguments in controlsource; requery the listbox after field
lstWLCriteria changes.


Vadim

 

[GLock]

What should I put as the rowsource of the listbox?

I tried the following, and got an error message "Must declare the variable
'lstWLCriteria'

Exec WL_Nxt_stp_sfrm_sp @lstWLCriteria

 

[Vadim Rapp]

G> What should I put as the rowsource of the listbox?

WL_Nxt_stp_sfrm_sp


Vadim

 

[liardet]

This is an example coming from an Access 2003 ADP database linked to SQL server 2000

Private Sub FlowHistory_CB_GotFocus()
Dim part As Integer
part = Me.PKPart.Value
Me.FlowHistory_CB.RowSource = " exec SP_Flow_Part'" & part & "'"
End Sub

FlowHistory_CB is a drop-down combination box in a formular
SP_Flow_Part is a Stored Procedure expecting a parameter "part"
when you click on FlowHistory (event GotFocus occurs) , the result of your query appears inside taking into account the parameter coming from a field of your formular (part = Me.PKPart.value)

Be careful to add " ' " on both side of parameter. " exec name of stored procedure ' " & parameter & ' "