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 - 19 Sep 2003 18:37 GMT
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
>-----Original Message-----
>I am trying to use a SQL Stored Procedure as the rowsource for my listbox
[quoted text clipped - 16 lines]
>
>.
Vadim Rapp - 19 Sep 2003 19:20 GMT
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
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com
GLock - 19 Sep 2003 19:51 GMT
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
> G> Exec WL_Nxt_stp_sfrm_sp @crit_id =
> G> Forms!frmWatchlist!lstWLCriteria
[quoted text clipped - 9 lines]
> 847-685-9073
> www.vadimrapp.com
Vadim Rapp - 19 Sep 2003 21:56 GMT
G> What should I put as the rowsource of the listbox?
WL_Nxt_stp_sfrm_sp
Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com