[AccessD] How to Use SQL Server Stored Procedure as a Rowsource for Listbox

Too bad you're not working with an ADP, or it would be as simple as:

me.lstSomeListBox.rowsource = "stpSomeStoredProcedure"

I guess, what you want to do is write a VBA function then call it on the
after update event of both comboboxes.

Warning, aircode:


Private Sub UpdateListBox()

Dim qdf As QueryDef
Dim strSQL as String

strSQL = "EXEC dbo.stpSomeStoredProcedure @Parm1=" & me.comboBox1
Set qdf = CurrentDb().QueryDefs("MyQueryNameHere")
qdf.SQL = strSQL


me.listbox1.Requery

End Sub


Of course, you'll have to add the 2nd parameter, trap for when they are
empty fields and such, but this should get you going.

HTH
David


On Thu, Aug 2, 2012 at 12:56 PM, Jim Hewson <jm.hwsn at gmail.com> wrote:

> I'm lost!
> Well actually that's my normal state usually.
> I need help...
>
> Everything I've done in the last 3 years has been in Access 2007.  I
> haven't touched SQL Server at all for that long.
>
> For a new requirement I need to run a stored procedure (SPROC) and populate
> a list box.
> The form has two combo boxes that will be the parameters that is passed to
> the SPROC.
> I have successfully (I think), opened the database, passed the
> parameters and run the SPROC.
> I can't figure out how to populate the list box.
> I'm a pretty simple guy.  The code I got from SQL Server administrator is 8
> pages long... several functions and a class.
> That just seems too complicated for this.
>
> The process I'm trying to do is:
> Choose the parameters for the first SPROC
> Execute the SPROC
> Populate a list box.  -- this list box will be the parameter for the second
> SPROC.
>
> What is the simplest way to populate a list box?
>
> Thanks,
> Jim
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>