function as criteria for a query

[Paul]

i've successfully used a function result as the criteria for a query in
access mdb database (as in KB210388) but i cant use the function in a similar
query in an ADP database. I get the error: "ADO error 'functionName()' is not
a recognized function name". Can anyone show me how to sort this out!

 

[Duane Hookom]

As you have experienced, you can't use an Access function in an ADP
query/view. SQL 2000 does have the ability to create user-defined functions.
Since we know nothing about your function, we can't offer alternative
syntax.

 

[Paul]

What i'm trying to do is base an SQL (stored procedure / view) query
on the Public variable "userId" so that various forms
can display records relevant to the 'logged-in' user.

This method works in an MDB query like a charm!
What i'd like to do is use a similar method
the ADP version of the database using sql server 2000 (desktop engine)

I have created a public variable called "userId" it stores the user id number
which was entered in a text box on a 'log-in' form.

The function i've created uses the following syntax (as recomended in
KB210388)

Public Function fnUserId()
fnUserId = userId
End Function

The syntax of the stored procedure is as follows:

SELECT dbo.tblUsers.*
FROM dbo.tblUsers
WHERE (Id = fnUserId())

Error message returned "ADO error 'fnUserId' is not a recognized function
name"

How do I get the stored procedure / view to base the value in the WHERE
clause that is being held
held in the public variable "userId"?

 

[Duane Hookom]

Write the value to a table with a single record in SQL Server.

 

[Paul]

Thanks for your help. Looks like i've solved it now.

Thanks very much

paul