Passing Parameters To SPs

  • Thread starter Thread starter MikeC
  • Start date Start date
M

MikeC

I'm trying to use a stored procedure as a row source for a combo box in an
Access 2002 project (ADP). An MSDE 2000 database is the back end. So far,
simple stored procedures work just fine. However, if I enter the stored
procedure together with a parameter, then Access displays a runtime error
telling me that I've misspelled the record source name.

For Example:

Works Fine:
sp_ClassList

Displays Runtime Error:
sp_ClassList @ExcludeClassID = Forms!MyForm!cmbClassID

Is there a way to pass a form's control reference as a parameter in the
combo box's property sheet or can this only be done via code?

I'm certainly not opposed to writing code, but I would like to avoid
spending extra time writing code if there is an easier way to accomplish the
above.
 
Great idea. This brings me one step closer. Now I'm getting a different
error, but at least I was able to verify that a parameter can be
successfully passed (as a hard-coded value).

Works:
EXEC sp_ClassList 1

Displays Runtime Error:
EXEC sp_ClassList Forms!MyForm!cmbClassID
EXEC sp_ClassList Forms!MyForm!ClassID

Here's the runtime error:

"Invalid SQL Statement. Check the server filter on the form record
source"

The form's "Server Filter" property is Null and the "Server Filter By Form"
property = "No".

Below is the content of the stored procedure. Remember, this stored
procedure works fine if I pass an integer as the parameter. I get the error
only when I reference a control or the underlying field as shown above.

ALTER PROCEDURE sp_ClassList @ExludeClassID Int = 0
AS
SELECT ClassID, ClassTitle
FROM dbo.tblClass
WHERE ClassID <> @ExludeClassID
ORDER BY ClassTitle

Other ideas?
 
Although the ! should work, try Forms("MyForm")("cmClassID")

The other option is to set it in code. When the form open use:

cmbBox.RowSource = "EXEC sp_ClassList " & Forms("MyForm")("cmbClassID")

Just throwing out ideas here....

Jim
 
I've just finished an exhaustive amount of testing and have concluded that
references to other controls cannot be converted to a parameter value when
used in the property sheet. On the other hand, as I mentioned below,
hard-coded values do work. I'll just use VBA.
 
Back
Top