2 questions about reading value from scalar UDF

A

Abe

I'm new to adp but am pretty proficient with vba and Access dao development. I am trying to use roles to dynamically control my forms with the UDF that I created to report back which role a user is grouped into

CREATE FUNCTION dbo.GetRol

@Role_Name varchar(12) = '
)
RETURNS varchar(12)
A
BEGI
IF (is_member('dbo') = 1
set @Role_Name = 'dbo
ELS
IF (is_member('CTS_MGMT') = 1
set @Role_Name = 'CTS_MGMT
ELS
IF (is_member('CTS_PM') = 1
set @Role_Name = 'CTS_PM
ELS
IF (is_member('CTS_RO') = 1
set @Role_Name = 'CTS_RO
ELS
IF (is_member('CTS_Finance') = 1
set @Role_Name = 'CTS_Finance
RETURN @Role_Nam
EN
I have two questions:
1) Why if I try to run the function from "queries" or try to open a recordset based on this function it acts like a parametized query and askes for a value for @Role_Name
an
2) How do I retrieve the value returned in Access? If I use a recordset, no label is visible (i.e. rst!GetRole does not work) ( select dbo.getrole('') from query analyzer works, but as before requires a parameter - the ""
Thank you very much for any insight as to how this should work.
 
Top