Passing Parameters To SPs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeC - 23 Nov 2004 07:46 GMT

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.

Reply to this Message

J. Clay - 23 Nov 2004 21:22 GMT

For your rowsource try EXEC sp_ClassList Forms!MyForm!cmbClassID

Jim

> 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,
[quoted text clipped - 16 lines]
> spending extra time writing code if there is an easier way to accomplish the
> above.

Reply to this Message

MikeC - 23 Nov 2004 22:30 GMT

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?

> For your rowsource try EXEC sp_ClassList Forms!MyForm!cmbClassID
>
[quoted text clipped - 22 lines]
> the
> > above.

Reply to this Message

J. Clay - 24 Nov 2004 16:12 GMT

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

> 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
[quoted text clipped - 56 lines]
> > the
> > > above.

Reply to this Message

MikeC - 05 Dec 2004 04:27 GMT

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.

> Although the ! should work, try Forms("MyForm")("cmClassID")
>
[quoted text clipped - 73 lines]
>> > the
>> > > above.

Reply to this Message