Stored Proc as Listbox source Access2000

[Guest]

Hi,
I am using access2000 and SQL 2000. I have a dropdown
listbox on my form that allows a user to select the
subjects they are interested in. I would like to use a
stored procedure to pull back the list and restrict it so
that options that have already been chosen cannot be
chosen again. I have already written the stored procedure
and this works OK in SQL query analyser.

CREATE PROCEDURE [dbo].[UDP_SubjectAreaList]
(@ContactID [int])
AS

SELECT SubjectAreaID, SubjectArea
FROM TBL_SUBJECTAREA
WHERE SubjectAreaID NOT IN
(

SELECT SubjectAreaID
FROM TBL_CONTACTSUBJECTAREA
WHERE (ContactID = @ContactID)

)

as you can see the stored procedure takes 1 parameter, but
there does not appear to be any way to pass this parameter
from the listbox control as there is no 'input parameters'
property to set.

It will not let me append the value of contactID to the
end of the rowsource, but this is where it gets a little
strange; If I do not supply a parameter at all the SP
works, but does not restrict the results by ContactID as
required. If I run the SP from Query analyser and do not
supply a parameter then the query breaks. This makes me
wonder if it is passing a value somehow, just not the
correct one.

Is there a way that I can set the parameters to the SP for
the listbox?

Thanks in advance

 

[J. Clay]

Set the Rowsource like this:

Me!ListBox.RowSource = "EXEC StoredProcedure '" & Parameter & "'"


HTH,
J. Clay