p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Passing parameters to Stored Procedures in Access 2000


Message #1 by brian.skelton@b... on Tue, 11 Sep 2001 17:52:30
I'm in the process of upgrading an Access 97 database to an Access 2000 

project.



As well as converting all my code to ADO, I'm trying to replace all the 

little SQL statements I come across with stored procedures or views.



What currently has me stumped is something that's so simple in Access 97 

that I cannot believe it's not possible in an Access project.



I have a combo box on my form. Which items are displayed on the list is 

controlled by the value of another control. The combo box has a rowsource 

something like this:



SELECT tblTrainingCourses.fldTrainingCourseUID, 

tblTrainingCourses.fldTrainingCourse 

FROM tblTrainingCourses 

WHERE tblTrainingCourses.fldCourseTypeUID=Forms![frmTraining]!

[cmbCoursetype]

ORDER BY tblTrainingCourses.fldTrainingCourse



I would like to replace this select statement with a call to a stored 

procedure that looks like this:



Alter Procedure spcmbCourseTitle



@intCourseTypeUID int

	

As

set nocount on

SELECT tblTrainingCourses.fldTrainingCourseUID, 

tblTrainingCourses.fldTrainingCourse 

FROM tblTrainingCourses 

WHERE tblTrainingCourses.fldCourseTypeUID=@intCourseTypeUID

ORDER BY tblTrainingCourses.fldTrainingCourse

return



Which is fine - except that I cannot work out how to set the stored 

procedure parameter to Forms![frmTraining]![cmbCoursetype].



Surely this can be done?



I've started working on a way to do this through VBA - but it's not going 

to be elegant and I'd like a simpler solution.



Thanks in advance



-BDS

  Return to Index