Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / SQL Server / ADP / September 2003

Tip: Looking for answers? Try searching our database.

SQL Stored Procedure as Rowsource for listbox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
GLock - 19 Sep 2003 17:46 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.